VBA to Delete rows with Zero values Loop

[SAMCRO2014]

I have search previous threads and have tried different coding to delete the entire row of data if the value in column M (12) is zero to no avail. Getting very frustrated as this should be pretty simple coding. I must have a syntax error somewhere as it will only delete one row at a time when I run the macro.

Sub CleanSPS()
'
' CleanSPS Macro
'
Dim SPS As Worksheet
Set SPS = ActiveWorkbook.Sheets("SPS+")

Dim LastRowSPS As Long
LastRowSPS = SPS.Cells(Rows.Count, 12).End(xlUp).Row

Dim p As Long ' for projection
'Delete rows that contain a projection value of "0" in column L

For p = 3 To LastRowSPS

If Cells(p, 12).Value = 0 Then
Cells(p, 12).EntireRow.Delete
End If

Next p

MsgBox "Finished"

End Sub


Can you see my error?

 

[Fluff]

When deleting rows you need to loop backwards. try

For p = LastRowSPS To 3 Step -1
   
   If SPS.Cells(p, 12).Value = 0 Then
      SPS.Rows(p).Delete
   End If

Next p

 

[JackDanIce]

Your code is designed to delete one row at a time because your loop counter is increment sequentially +1 each time

For p = 3 to LastRow
If Cells(p, 12).Value = 0 Then Cells(p, 12).EntireRow.Delete
Next p

Part in red is a single cell with p being a variable that increases sequentially (i.e. +1) so it is going to delete one row at a time. It's probably doing this wrong and should be:

For p = LastRow to 3 Step-1
If Cells(p, 12).Value = 0 Then Cells(p, 12).EntireRow.Delete
Next p

This is one suggestion to delete in a single call:

With Cells(3, 12).Resize(LastRow - 2)
    .Replace 0, ""
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With

 

[SAMCRO2014]

I knew it was something so simple I was missing. Thank you!!!

 

[Fluff]

Glad we could help & thanks for the feedback