In my Excelsheet I have a row (9) which includes drop-down buttons which can select things like sum etc. How can I check if a row contains these buttons using VBA?
I have tried "HasValidation" and "If ws.Rows(i).AutoFilter" but were not successful
As stated in the comment, your "row with drop down buttons" is not regular row, it's a totals row of a table. The drop down buttons have nothing to do with data validation, therefore your attempt using HasValidation
fails.
You can switch on/off this row in the table design:
Now you can get information about the ranges occupied by a table using VBA. First thing is you need to know that a table in VBA is named ListObject, and you can access all tables of a worksheet using the ListObjects
-collection. The following code assumes that you want to know the details of the first table of the first sheet, adapt to your needs.
Dim wb As Workbook, ws As Worksheet, table As ListObject
Set wb = ThisWorkbook
Set ws = wb.Sheets(1)
Set table = ws.ListObjects(1)
You can check the ranges of the object table
. If a table has the totals row enabled, the property ShowTotals
is set to True and you can get the address of it using the TotalsRowRange
-property.
Debug.Print "Table Range: ", table.Range.Address
Debug.Print "Header Row: ", table.HeaderRowRange.Address
Debug.Print "Data Range: ", table.DataBodyRange.Address
If table.ShowTotals Then
Debug.Print "Total Row: ", table.TotalsRowRange.Address
Else
Debug.Print "Total Row: ", "(no total row)"
End If
Using the following table, the result is
Table Range: $A$1:$H$9
Header Row: $A$1:$H$1
Data Range: $A$2:$H$8
Total Row: $A$9:$H$9
So table.TotalsRowRange.Row
would give you the desired row number (=9). However, in most cases you don't need that. You failed to explain what your ultimate goal is, but I assume you want to add a row above this total row, and that can easily be done with table.ListRows.Add
which will create an empty row above the totals row.