Find all zeros in a column then delete first three cells in row

[jamer02]

Hey guys,

I am looking to create a macro that finds all 0's in the column A and then once it finds this zero, it then deletes the content of the first three cells in that row. Then once it has done this the loop will stop.

Any ideas would be much appreciated.

Jamer

 

[Desu Nota from Columbus]

Please clarify the following:

1) You want to search column A for cells that equal 0.

2) For Each cell that equals 0 you want the next 3 cells in that row deleted--meaning Columns B, C, D? For example, A15=0 so clear contents of B15,C15, D15?

3) Are there any blanks within your data in Column A?

 

[Andrew Poulsom]

Try:

Sub Test()
    With Range("A1").CurrentRegion
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="=0", Operator:=xlAnd
        .Offset(1).Resize(, 3).SpecialCells(xlCellTypeVisible).ClearContents
        .Parent.AutoFilterMode = False
    End With
End Sub

 

[Sektor]

[COLOR="Blue"]Sub[/COLOR] StrangeLoop()

    [COLOR="Blue"]Dim[/COLOR] i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR]
    
    [COLOR="Blue"]For[/COLOR] i = 1 [COLOR="Blue"]To[/COLOR] Range("A1").End(xlDown).Row
        [COLOR="Blue"]If[/COLOR] Cells(i, "A") = 0 [COLOR="Blue"]Then[/COLOR]
            Cells(i, "A").Offset(0, 1).Resize(, 3).ClearContents
            [COLOR="Blue"]Exit[/COLOR] [COLOR="Blue"]For[/COLOR]
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
    [COLOR="Blue"]Next[/COLOR]

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[Desu Nota from Columbus]

Sub DelZeros()

Dim r as Long
Dim lrow as Long

lrow = Range("A" & Rows.Count).End(xlUp).Row

For r = lrow to 1 step -1

If Cells(r, 1) = 0 Then
     Range("B" & r & ":" & "D" & r).ClearContents
End if
Next r

End sub