none
VBA Table Row Referencing RRS feed

  • Question

  • Hi All,

    Can help with the correct syntax to obtain the activecell.databodyrange row value for an excel table? 

    cRow = ActiveCell.ListObjects("Table1").DataBodyRange.Rows.Value

    Cheers

    Brad

    Monday, February 8, 2016 3:15 PM

Answers

  • Do you want the values from the entire row?

    Sub TestMacro()
        Dim cRow As Range
        Dim c As Range
        Set cRow = Intersect(ActiveSheet.ListObjects("Table1").DataBodyRange, Selection.EntireRow)
        For Each c In cRow
            MsgBox c.Value
        Next c
    End Sub


    • Marked as answer by _Brad_C_ Monday, February 8, 2016 10:36 PM
    Monday, February 8, 2016 8:27 PM
  • Sorry - that is just

        Dim cRow As Long
        cRow = Selection.Row - ActiveSheet.ListObjects("Table1").HeaderRowRange.Row
        MsgBox cRow

    • Marked as answer by _Brad_C_ Tuesday, February 9, 2016 10:54 PM
    Tuesday, February 9, 2016 3:11 PM

All replies

  • Do you want the values from the entire row?

    Sub TestMacro()
        Dim cRow As Range
        Dim c As Range
        Set cRow = Intersect(ActiveSheet.ListObjects("Table1").DataBodyRange, Selection.EntireRow)
        For Each c In cRow
            MsgBox c.Value
        Next c
    End Sub


    • Marked as answer by _Brad_C_ Monday, February 8, 2016 10:36 PM
    Monday, February 8, 2016 8:27 PM
  • Do you want the values from the entire row?

    Hi Bernie

    No, not really.  I'm really just chasing the row number in the table.

    For instance if Range("D6") is selected, then cRow should equal 3.  To do this with just the worksheet I've been using cRow=ActiveCell.Row.  My focus is now a table and users move tables, for instance the above table could move to A1, or B6.  But Blake will always be in row 3. 

    Monday, February 8, 2016 10:50 PM
  • Sorry - that is just

        Dim cRow As Long
        cRow = Selection.Row - ActiveSheet.ListObjects("Table1").HeaderRowRange.Row
        MsgBox cRow

    • Marked as answer by _Brad_C_ Tuesday, February 9, 2016 10:54 PM
    Tuesday, February 9, 2016 3:11 PM
  • Hi Bernie,

    Just what I needed.

    With Sheet1.ListObjects("Table1")
    
    RowID = Selection.Row - .HeaderRowRange.Row
    
    MyVar = Intersect(.DataBodyRange.Rows(RowID).EntireRow, .ListColumns("Column_Name").DataBodyRange).Value 
    
    End With
    

    I'm now able to reference the table column making the rearranging of my table and my VBA completely compatible.

    Thank You!

    Brad

    Wednesday, February 10, 2016 3:25 PM