Seems like this should be a simple thing, and maybe it is, but I can't seem to find out how to do this. I want to keep track of which rows within the table certain cells are but I can't find a way to get the table row number rather than the worksheet row number.
When iterating through cells in a table column, how do I get the cell's row number within that table?
- browncoat
- Thread is marked as Resolved.
-
-
Is it an excel table or just area on the spreadsheet that you have placed a border around?
-
It's an Excel table. I want to be able to refer to other cells in that cells row in the table using table.range.cells(x,y)
-
Ok, well let's assume your table is called "MyTable" and exists in "Sheet1":
Code
Display MoreSub tableNav() Dim tbl As ListObject Dim rw As Long Dim cl As Long Dim bodyCell As Range Dim headCell As Range Set tbl = Sheets("Sheet1").ListObjects("MyTable") If tbl.ListRows.Count > 0 And tbl.ListColumns.Count > 0 Then For rw = 1 To tbl.ListRows.Count For cl = 1 To tbl.ListColumns.Count Set headCell = tbl.HeaderRowRange.Cells(1, cl) Set bodyCell = tbl.DataBodyRange.Cells(rw, cl) Debug.Print "Row: " & rw & " | Column: " & cl & " (" & headCell.Value & ")" Debug.Print "Current Cell: " & bodyCell.Address & " = " & bodyCell.Value Debug.Print "----------------------" Next cl Next rw End If End Sub
-
That works thanks! Think I got hung up on finding a specific function rather than thinking outside the box.
-
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!