VBA renumbering rows after deleting a row.

[James824]

Hi, so I also have a macro that if activated will delete the selected row and another that will add in a row at the bottom. The row has a heading that is numbered from 1 through to the number of rows. Currently, if a row is deleted half way within the table (for example), the numbering is messed up. If I then go to add in another row at the bottom, it tries to number the new row with a number that is already taken and an error pops up. Is there any way of implementing something that renumbers all the remaining rows after deleting one row, which I believe would solve this problem. Any help with this would be greatly appreciated.

Many thanks,
J

 

[hnsd24_CN]

You can define an array and use the "for" function to put new numberings loop into it. Then written the array to the column where numberings is located.

 

[GWteB]

Another way is to use a formula for your row numbers ...

Book1
	M	N	O	P
27	0	col1	col2	col3
28	1
29	2
30	3
31	4
32	5
33	6
34	7
35	8
Sheet1

Cell Formulas
Range	Formula
M28:M35	M28	=M27+1

 

[James824]

That's great! Many thanks for you advice. The code seems to work the first time that I use the buttons to complete the previously mentioned actions, however, after any other time, the following message keeps popping up. "Run-time error '1004': Table features aren't available because the sheet is protected". I believe that the sheets should be unprotected when it runs into this as I have the following in place at the start of the sub.
ActiveSheet.Unprotect Password:="password"

Could there be something that I am missing with this?

Many thanks,
J

 

[GWteB]

Strictly speaking, the ActiveSheet can be any worksheet. To be sure make a proper reference to the worksheet you're addressing.
For example, provided the code is in the same workbook as that particular worksheet :

ThisWorkbook.Sheets("SheetWithTable").Unprotect Password:="password"