Updating serial in row

[Sheroo318]

I would like that when user selects the row in active worksheet and clear its contents, that subsequent cells will update itself with the new serial number
for e.g.
if column A range A1 to A5 has serials 1 to 5 and user decides for A3 to clear its contents, then I would like that A4 will show the new serial 3 and A5 showing 4 (instead of the original value 4 and 5 respectively)

 

[Andrew Poulsom]

Maybe events are disabled. In the VBE Immediate window type:

Application.EnableEvents = True

and press Enter.

 

[Sheroo318]

Hey Andrew,
the part where if .value = "" then entirerow delete doesn't work or at least most of the time it doesn't
I have replaced the "" with "a" and then it will detect the change and carry out the delete of the row, but with the phrase if blank is not scanning or detecting the function.
Any thoughts?

 

[Andrew Poulsom]

Are you sure that the cell is really blank?

 

[Sheroo318]

yes, i even just created new sheet and would insert the numbering in column A only

 

[Andrew Poulsom]

Did your new worksheet have the Worksheet_Change event procedure in its code module?

 

[Sheroo318]

yes, i just took your code copy and paste.
It sometimes will work once and stop updating and usually it iwll just update the numbering sequence but not delete the rows..only if i changed the .value = "" to .value = "a" or any other text, then excel will properly detect the code (by checking if value is a or not) and delete the rows

 

[Andrew Poulsom]

The code worked for me when I tried it.