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]

Welcome to MrExcel.

Perhaps in A2 copied down:

=MAX(A$1:A1)+1

 

[azumi]

Doops deleted post

 

[Sheroo318]

Welcome to MrExcel.

Perhaps in A2 copied down:

=MAX(A$1:A1)+1

Dear Andrew,
Thanks..
It exactly has done what i am looking for, will play more with it to meet the criteria of deleting the cleared contents cells up
But still, one problem here, after i change a selected cell for few times, it loses its function.

I would also like to use the function in vba

 

[Sheroo318]

Sub row()
Dim a As Long
Dim b As Long



a = Range("A1").CurrentRegion.Rows.Count
b = a - 2
If Worksheets("Sheet1").Rows(b).ClearContents Then
Rows(b).Delete shift:=xlUp
Range("A" & b).Rows.Value = b
End If
End Sub

that is what i have reached so far. But i want to apply it to the rest of the subsequent rows as well, not only the new row

 

[Sheroo318]

Sub onupdate()
Dim a As Long
Dim b As Long
a = Range("A1").CurrentRegion.Rows.Count
b = a - 2
If Worksheets("Sheet1").Rows(b).ClearContents Then
Rows(b).Delete shift:=xlUp
End If
With Range("A2:A" & a)
.FormulaLocal = "=max(A$1:A1)+1"
End With

End Sub

Function/sub as a standalone is working fine...how can i have it in a private sub that it will run only on update or on change for a particular column
Any other suggestion is appreciated

 

[Andrew Poulsom]

What is the purpose of this line?

If Worksheets("Sheet1").Rows(b).ClearContents Then

Is Sheet1 the ActiveSheet?

 

[Sheroo318]

Yes, it is the active sheet

 

[Andrew Poulsom]

You haven't answered my first question. What do you think that line is doing? As far as I know the ClearContents method always returns True.

 

[Sheroo318]

Well if i clear the contents of a row then it will just delete the row and update the serial. If no clear selection was carried out, then no rows are shifted upwards