VBA: Referring to current row in Excel Table

Question

Good Morning

I'm trying to refer to the current row in a table but whatever I try, nothing works. I understand that the table model doesn't have a direct route to the information.

My issue is that whatever I plug in here, the result for ListRow is 0.

Dim r As Long

r = ActiveCell.row

Dim tableRow As Long

tableRow = tblCustomer.HeaderRowRange.row

Dim ListRow As Long

ListRow = ActiveCell.row - tableRow

It's too weird because on my last run

• r = 5
• ActiveCell.row = 10
• tableRow = 5
• tblCustomer.HeaderRowRange.row = 6

So if I plug in numbers into above code, it would read like this:

r = ActiveCell.row

5 = 10

tableRow = tblCustomer.HeaderRowRange.row

5 = 6

ListRow = ActiveCell.row - tableRow

0 = 10 = 5

What is going on?

Does anybody know how to solve this?

Thanks,

Christine

Answer 1

What do you want to do next?
r=10

TableRow=6

ListRow=4

Answer 2

Good question. It's not the result was getting. I get the right result now but I'm not quite sure I can trust it ...

I think I have to revert my code back to absolute rather than table references and keep adjusting for any changes.

Thanks for confirming that I'm not going mad.

Answer 3

You are welcome. Feel free to share any update:)