Auto record number - need to delete and re-calculate

[HeyInKy]

I have the following code to add a 'record number' in column A, and a 'name" (from a text box entry) in Column B for each new row of data which is input from a userform:

Please Login or Register  to view this content.

FYI, my first row of data is constant and cannot be deleted, and begins with a "1" in cell $A$2 (Row 1 is a header and contains other values/data).

I then have another userform and routine where the any names added in column B (a Range named "NameList") can be deleted, after being selected from a combo box:

Please Login or Register  to view this content.

So I may have a sheet that look like this:
A B
Header Row...
1 Former Employee
2 Bill
3 Bob
4 Becky
5 Beth

My problem is that I also need to delete the corresponding record number, and if the record number is in the middle, i.e., say "Bob" was deleted from above, that not only would the "3" be deleted, but the records numbers would also shift up and recalculate to the correct number (in this example, "4" would become "3" and "5" would become "4").

Any help is greatly appreciated!

-HeyInKy

[Leith Ross]

Hello HeyInKy,

Since the record number equals the row number, just add the formula =ROW() to row 2 down. When the row is deleted the formula will recalculate.

[HeyInKy]

It actually does not equal the row number, but I see where you're going... perhaps ROW()+2 would work?

Thanks!

[HeyInKy]

Worked! Thanks!

Follow-up question... what would my VBA code look like to find the last cell in the column with a value and then delete that value?

Please Login or Register  to view this content.

??

[HeyInKy]

This seemed to work:

Please Login or Register  to view this content.