VBA renumbering rows after deleting a row.

James824

New Member
Joined
Oct 14, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

hnsd24_CN

Board Regular
Joined
Oct 13, 2020
Messages
78
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Another way is to use a formula for your row numbers ...

Book1
MNOP
270col1col2col3
281
292
303
314
325
336
347
358
Sheet1
Cell Formulas
RangeFormula
M28:M35M28=M27+1
 

James824

New Member
Joined
Oct 14, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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 :

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

Watch MrExcel Video

Forum statistics

Threads
1,122,297
Messages
5,595,301
Members
413,985
Latest member
daniel1969

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top