Delete rows with 0 in target cell

  • Thread starter Thread starter Shawn
  • Start date Start date
S

Shawn

Below is a handy code I have used to serch a range and delete all cells with
a 0. I need to modify this slightly so it will not only delete the cells
with 0, but all the cells with 0 along with the four cells to the left of
that cell. For example, if cell F5 has a 0 in it, it will delete cells A5:F5.

Dim xRange As Range
Dim xCell As Range

For Each xCell In Sheets("SDII").Range("F2:F34")
If xCell.Value = 0 Then
If xRange Is Nothing Then
Set xRange = xCell
Else
Set xRange = Union(xRange, xCell)
End If
End If
Next xCell
xRange.Delete Shift:=xlUp
 
replace
xRange.Delete Shift:=xlUp
by
xRange.offset(,-4).resize(,5).Delete Shift:=xlUp

shouldn't this be INSIDE your FOR...NEXT loop?
 
Back
Top