excelvbabutton

Find Cells with Button in them


In my Excelsheet Excel Sheet 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


Solution

  • 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:

    How to switch on and off the Summary row of a table.

    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

    Example table

    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.