Conditionally clearing a cell

[Guest]

How would I clear the contents of a cell, if the cell to the left of it didn't contain a value greater than zero, then proceede the the cell below that one and repeat the same action and so forth until there were no more values to check. I'm stumped. Thanks for any help...

Don Rountree

 

[J.E. McGimpsey]

One way:

Public Sub ClearIfCellToLeftIsNotPositive()
Dim rCell As Range
Dim rClear As Range

For Each rCell In Range("B1:B" & _
Range("B" & Rows.Count).End(xlUp).Row)
With rCell
If .Offset(0, -1).Value <= 0 Then
If rClear Is Nothing Then
Set rClear = .Cells
Else
Set rClear = Union(rClear, .Cells)
End If
End If
End With
Next rCell
If Not rClear Is Nothing Then _
rClear.ClearContents
End Sub