VBA : Count sequentially in a table column, all the way down to the end of the table

[jase71ds]

Thanks for any help on this - I appreciate the gift of time and knowledge that so many on this forum freely give

I have an Excel Table named "tbl_Schedule"
The first column is named "Counting"

The Counting column's purpose to start with 1 and count sequentially all the way down the table to the very last row in the table.

The table has nearly 2,000 rows and each week I'm having to insert new rows within the middle of the table.
Each time I do this I have to re-number.

I'd like to have a macro that, upon execution, starts with 1 in the first row after the column header: "Counting", and counts sequentially down to the last row of tbl_Schedule.
Keep in mind, because I will run this macro after having just inserted new rows somewhere within the table, I can't use the VBA trick of looping until it finds the first blank cell (because the new rows will have blank cells). I need the macro to look at the true range of the Excel Table and know how far down to count.

Hope that was clear.
As stated earlier - any help is very much appreciated.

Jase.

 

[igold]

Does this do what you want...

Sub InsertRowNum()


    Dim tbl As ListObject
    Dim x As Long


    Set tbl = ActiveSheet.ListObjects("tbl_Schedule")
    For x = 1 To tbl.ListRows.Count
        tbl.DataBodyRange(x, 1) = x
    Next x
    
End Sub

 

[jase71ds]

Thank you very much. That seems to work perfectly :D
Much appreciated!!

 

[igold]

Great, you're welcome. Thanks for the feedback!

 

[jase71ds]

igold - I have a follow-up question.
The code populates the first column of the table - which is exactly what I needed - but I'm wondering for future modification, how might I modify this code if I ever wanted it to do the same action, but on say the 4th column of the table, named "ActivityNumber"?
Thanks.

 

[igold]

The line tbl.DataBodyRange(row,column) for each data point in the table

This is a real good reference page for dealing with tables... Real easy to follow...

https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables

 

[jase71ds]

Wow! What a great resource! Thanks so much.

 

[igold]

No problem. Happy Holidays.