Populate first column with row index in excel table using vba

Question

Hi,

I have an excel table for which I am trying to insert a column with a row index that is dynamically updated when a new row is inserted in the table using VBA. I need help

Thank you in advance

Answer 1

If you want to insert a new column that will automatically show 1, 2, 3, etc. even after inserting and deleting rows, then try this code:

Dim ws As Worksheet  
Set ws = Sheet1 ' TODO: use the correct worksheet '  
Dim t As ListObject  
Set t = ws.ListObjects("Table1") ' TODO: use the correct table '  
Dim c As ListColumn  
Set c = t.ListColumns.Add(1)  
c.Name = "Row Index"  
t.DataBodyRange(1).Formula = "=ROW()-1"  

It assumes the the first row is used for headers.

But it can be also done manually.