CODEWITHSUNDEEP
excelexcel-formulaexcel-tablesvba
PoorChristmas
PoorChristmas

Reputation: 55

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.

Upvotes: 1

Views: 6527

Answers (2)

PoorChristmas
PoorChristmas

Reputation: 55

This could be another solution:

 Dim rngTable As Range
Dim country As Range
Set rngTable = Range("Table1[Country]")

    rngTable.Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    For Each country In Selection.Cells
        MsgBox country.Row
    Next

Upvotes: 1

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

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.

Upvotes: 1

Related Questions