find table row number from selected cell

Question

How do I find the row number in a table (Excel 2010) from the selected cell.
I can find the sheet row number from ActiveRow.Row or Selection.Row. But I want to know what row number in the table this is.

Flivver

Answer 1

    Selection.Row - Selection.ListObject.Range.Row

Ostium

Awesome. Thanks! ➕💯 – Blate

Answer 2

Here's an idea, try getting (active row - first row of table). That will give you the row number from the table.

Ngo

Answer 3

i am not a vba / excel expert but this might do the job:
the answer is a bit late - but i ran into the same problem.
my function returns a listRow object, that is more powerful:

Sub testit()
    Dim myList As ListObject
    Dim myRow As ListRow
    'some reference to a listObject
    Set myList = ActiveWorkbook.Sheets(1).ListObjects("TableX")
    '
    'test the function
    Set myRow = FirstSelectedListRow(myList)
    '
    'select the row
    myRow.Select
    'get index within sheet
    MsgBox ("sheet row num " & myRow.Range.Row)

   ' get index within list
   MsgBox ("List row index " & myRow.Index)
End Sub
'return ListRow if at least one cell of one row is acitve
'return Nothing otherwise
Function FirstSelectedListRow(list As ListObject) As ListRow
    'default return
    Set FirstSelectedListRow = Nothing
    'declarations
    Dim activeRange As Range
    Dim activeListCells As Range
    Dim indexSelectedRow_Sheet As Long
    Dim indexFirstRowList_Sheet As Long
    Dim indexSelectedRow_List As Long
    'get current selection
    Set activeRange = Selection
    Set activeListCells = Intersect(list.Range, activeRange)
    'no intersection - test
    If activeListCells Is Nothing Then
        Exit Function
    End If
    indexSelectedRow_Sheet = activeRange.Row
    indexFirstRowList_Sheet = list.Range.Row
    indexSelectedRow_List = indexSelectedRow_Sheet - indexFirstRowList_Sheet
    Set FirstSelectedListRow = list.ListRows(indexSelectedRow_List)
End Function

Flooring

Answer 4

This might help, assuming that there is only one table in sheet. Otherwise You need to specify the table range.

Sub FindRowNoInTable()

Dim ObjSheet As Worksheet
Dim startRow, ActiveRow, ActiveCol
Dim ObjList As ListObject
Set ObjSheet = ActiveSheet
ActiveRow = ActiveCell.Row
ActiveCol = ActiveCell.Column
For Each ObjList In ObjSheet.ListObjects
    Application.Goto ObjList.Range
    startRow = ObjList.Range.Row
Next
MsgBox (ActiveRow - startRow)
Cells(ActiveRow, ActiveCol).Select

End Sub

Experienced

Answer 5

YourRange.row minus YourListObject.HeaderRowRange.row

Albuminoid

Answer 6

A little late, but I landed here whilst searching for this, so ...

Simplest way I know:

    dim MyTable as listobject
    dim MyRow as integer

    ' Here your code or a manual action makes you end up on a random row
    ... Dostuffdostuffbleepbloop ...

    ' Find the row number you landed on within the table (the record number if you will)
    MyRow = MyTable.DataBodyRange().Row

That's it.

Pease note:

• This is done with Office 2019
• The brackets after DataBodyRange are EMPTY.
• The row counter is from DataBodyRange thus does NOT include the headers

Hope it helps someone.

Vish