Search code examples
excelexcel-formulaexcel-tablesvba

Get Row number from each cell in selected range in Table with VBA?

Well. If I have for example a table with a column "countries" and other one "cities" And I make a Filter on Countries. How I can get the row number from each register filtered using VBA?

enter image description here

I like to get rows numbers: 2 and 6.

enter image description here

Thanks in advance.

Solution

  • Use the ListRows method of your table, looping through each row.

    In this case you can print all the rows in the table that passes your criteria of Not ...Hidden.

    Sub printUnhiddenRows()

    Dim r As ListRow, tbl As ListObject
    Set tbl = ThisWorkbook.Worksheets(1).ListObjects("Table1")
    
    For Each r In tbl.ListRows
        If Not r.Range.Rows.Hidden Then Debug.Print r.Range.Row
    Next r

End Sub

    Some things to remember:

    • You may need to change the index number on Worksheets(1) if the sheet is not #1 in the worksheet collection.
    • You may need to change the table name on .ListObjects("Table1") if the table is named something else.