Need to return the row value in a table in Excel using VBA. Could be any table any where on sheet cannot use activecell

Question

Using VBA in Excel:

I have many sheets that have multiple tables. The only thing these tables have in common is a ProductID column and LotNumber column. The data in the Products ID column needs to be validated and or manipulated. I need a way to get the Table Row Index number of the current TABLE of the currently selected cell. I can not use ActiveCell reference because the table rows do not match the sheet rows. I can not use find the last used cell in a column because there are other tables in the way.

I need to be able to use this sub in many different workbooks.

Here is the code I have so far.

Const tblUNLABELED As String = "tblUnLabel"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    StopAppEvents
    Dim ws As Worksheet

    Set ws = ActiveSheet
    Dim intRow As Integer
    Dim strTest As String
    If Not Intersect(ActiveCell, _
                     ActiveSheet.Range(tblUNLABELED & "[LotNumber]")) Is Nothing Then
        'go to the product entered in this table row and Ucase(ProductID)
        strTest = ws.ListObjects(tblUNLABELED).Range(intRow, "[ProductID]")
        ws.ListObjects(tblUNLABELED).Range(intRow, "[ProductID]") = UCase(strTest)
    End If

    RestartAppEvents

End Sub

Any help would be welcomed.

Answer 1

This uses the Range's ListObject Property to tell you the column name and row number of the table that the ActiveCell is in. If the ActiveCell isn't in a table you get a message saying so. A row number of 0 means the ActiveCell is in the table's header row:

Sub PrintActiveCellTableData()

    Dim cell As Excel.Range
    Dim lo As Excel.ListObject
    Dim loCol As Excel.ListColumn
    Dim loRowNum As Long

    Set cell = ActiveCell
    If cell.ListObject Is Nothing Then
        Debug.Print "No ActiveCell ListObject"
        Exit Sub
    End If

    Set lo = cell.ListObject
    With lo
        Set loCol = .ListColumns(cell.Column - (.HeaderRowRange.Cells(1).Column) + 1)
        loRowNum = cell.Row - .HeaderRowRange.Row
    End With

    Debug.Print "Table Name: "; lo.Name; "  ListColumn: "; loCol.Name; "  Row:"; loRowNum

End Sub

Answer 2

If the selected cell is within one of your tables and your tables are bounded with empty cells then ActiveCell.CurrentRegion should give you a range that encompasses the table, and you can work from there.

ActiveCell.Row - ActiveCell.CurrentRegion.Row + 1

gives you the (1-based) row number of the active cell within the current table