Delete rows with 0 in target cell
 

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



--
Thanks
Shawn

Delete rows with 0 in target cell
 

replace
xRange.Delete Shift:=xlUp
by
xRange.offset(,-4).resize(,5).Delete Shift:=xlUp

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

"Shawn" wrote:

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



--
Thanks
Shawn