VBA - Stop deletion passed a certain row range

[Eios000]

Afternoon all,

I had this code that when I initially placed it in, stopped the entire row from being deleted if it was A4 and it worked fine.

For whatever reason (gremlins) it is no longer working and know matter what, the message box always appears. Does anyone know why? I've tried modifying it to be A3, however when tested it deletes A4 and then seems to work...

Sub RemoveLastRow()
If Range("A" & Rows.Count).End(xlUp) = ActiveSheet.Range("A4") Then 'stops deletion
    MsgBox "Cannot delete any further."
    Exit Sub
End If
ActiveSheet.Cells(Rows.Count, "A").End(xlUp).EntireRow.Delete 'selects the last row of the sheet and deletes
End Sub

 

[rlv01]

It's because "Value" is the default property of a range object, so you are comparing values instead of cell locations. You could change this

If Range("A" & Rows.Count).End(xlUp) = ActiveSheet.Range("A4") Then 'stops deletion

to this

If Range("A" & Rows.Count).End(xlUp).Address = "$A$4" Then 'stops deletion

or this

If Range("A" & Rows.Count).End(xlUp).Row <= 4 Then  'stops deletion

 

[Eios000]

Awesome, I used that bottom solution and it worked perfectly! Thanks!

 

[Larry Haydn]

I you mean to delete any row(s) after row 4, then you should create a loop.
Alternatively, you could find the last row, and if the row number is greater than 4, delete all the rows from row 5 till the last row.