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]

Are you wanting to check that a cell in column A is blank?

 

[Sheroo318]

yes, right now there is no problem with the sub...it is running pretty much OK..
but i want it to auto update if user decides to clear contents in column A or sthg. I sure can't use it with private sub selectionchange, so any ideas of a functional sub that can check on update to the column or anything similar

 

[Andrew Poulsom]

Try this in the module for the Worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Long
    If Application.Intersect(Target, Range("A1").CurrentRegion.Columns(1)) Is Nothing Then Exit Sub
    If Target.Row = 1 Then Exit Sub
    Application.EnableEvents = False
    With Target
        For r = .Rows.Count To 1 Step -1
            With .Cells(r, 1)
                If .Value = "" Then
                    .EntireRow.Delete
                End If
            End With
        Next r
    End With
    With Range("A1").CurrentRegion.Columns(1)
        .Offset(1).Resize(.Rows.Count - 1).Formula = "=MAX(A$1:A1)+1"
    End With
    Application.EnableEvents = True
End Sub

 

[Sheroo318]

Try this in the module for the Worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Long
    If Application.Intersect(Target, Range("A1").CurrentRegion.Columns(1)) Is Nothing Then Exit Sub
    If Target.Row = 1 Then Exit Sub
    Application.EnableEvents = False
    With Target
        For r = .Rows.Count To 1 Step -1
            With .Cells(r, 1)
                If .Value = "" Then
                    .EntireRow.Delete
                End If
            End With
        Next r
    End With
    With Range("A1").CurrentRegion.Columns(1)
        .Offset(1).Resize(.Rows.Count - 1).Formula = "=MAX(A$1:A1)+1"
    End With
    Application.EnableEvents = True
End Sub

I believe it is working perfectly fine as per the requirement
Thanks

 

[Sheroo318]

Just to clarify,
the interesect function, what is it actually doing?
Also, if i want to add the matching cells in cross referenced sheet, where do i add it in the code
thanks in advance

 

[Andrew Poulsom]

The Intersect function is testing that the cell(s) that changed are in column A.

I don't understand your second question, sorry.

 

[Sheroo318]

The Intersect function is testing that the cell(s) that changed are in column A.

I don't understand your second question, sorry.

So i want to use for example on sheet 2 the function = Sheet1!A3..so that when the function of deleting the row occurs on sheet 1, it shall update the rows as well on sheet 2. So where to put it in the module?

 

[Andrew Poulsom]

I would just use this formula on Sheet2:

=INDEX(Sheet1!A:A,ROW())

 

[Sheroo318]

Hey,
the entire row delete is not working if i am clearing contents of the cell?
Can you try again again on your worksheet please

 

[Sheroo318]

Hey,
the entire row delete is not working if i am clearing contents of the cell?
Can you try again again on your worksheet please

it is working fine now....i just had to reopen excel