VBA delete cells based on criteria

[Jammydan]

Hi

I have the following code, however it seems to stop after removing 1 row, it doesn't check the rest of the range. Can anybody help please?

Many thanks

[SIZE=2]
Private Sub Worksheet_Activate()
Dim RemoveOld As Range
Set RemoveOld = Range("C5:C41")
Application.ScreenUpdating = False
ActiveSheet.Unprotect

'**** DELETE OLD CONTRACTS *****
For Each Cell In RemoveOld
If Cell.Value > 0 And Cell.Offset(0, 10) >= 0 Then
Cell.EntireRow.Delete Shift:=xlUp
Application.Run "InsertRowsAndFillFormulas_caller"
End If
Next Cell
ActiveSheet.Protect
Application.ScreenUpdating = True
Range("E3").End(xlDown).Offset(1, 0).Select
End Sub
[/SIZE]

 

[JackDanIce]

I think because of the nature of how you're using your FOR LOOP. Try changing it to:

Private Sub Worksheet_Activate()
Dim RemoveOld As Range
Set RemoveOld = Range("C5:C41")
Dim i as Long
Application.ScreenUpdating = False
ActiveSheet.Unprotect
 
'**** DELETE OLD CONTRACTS *****
For i = 5 to 41 
 If cells(i, 3) > 0 And cells(i, 13) >= 0 Then
  Rows(i).EntireRow.Delete Shift:=xlUp
   Application.Run "InsertRowsAndFillFormulas_caller"
  i = i + 1
  End If
Next Cell
ActiveSheet.Protect
Application.ScreenUpdating = True
Range("E3").End(xlDown).Offset(1, 0).Select
End Sub

Basically, I think you're deleting a row and then running something called InsertRowsAndFillFormulas_caller (a macro?!) which is then placing something into the next position of i so the IF criteria fails and no more rows are deleted. Something like that anyway.

I'd use F8 to step through your code and turn on the locals watch to how the value of i changes which will tell you what row the macro is acting on.

Bits in red are my changes

 

[Jammydan]

Thanks, still doing the same thing but I think the problem is with the macro i call to replace the deleted lines, thanks though