Macro to clear Zeroes in Col A to C

[howard]

I have written code to clear zeroes in Col A to C

I get a type mismatch when running the code and the following code is highlighted

 If .Cells(i, 1).Resize(, 2) = 0 Then

See full code below

 Sub clear_Zero_Values()
     With Sheets(1)
   Dim lr As Long
Dim i As Long

  lr = .Cells(.Rows.Count, "A").End(xlUp).Row
  For i = lr To 1 Step -1
    If .Cells(i, 1).Resize(, 2) = 0 Then
      Cells(i, 1).Resize(, 2).ClearContents
    End If
  Next i
  End With
  
End Sub

It would be appreciated if someone could amend my code

 

[James006]

Hello,

If you are dealing exclusively with constants ...

Sub TestConstants()
    Columns("A:C").SpecialCells(xlCellTypeConstants, 23).ClearContents
End Sub

HTH

 

[Yongle]

otherwise try...

Sub clear_Zero_Values()
    Dim lr As Long, cel As Range
    With Sheets(1)
        lr = .Cells(.Rows.Count, "A").End(xlUp).Row
        For Each cel In .Range("A1:C" & lr)
            If cel = 0 Then cel.ClearContents
        Next cel
    End With
End Sub

 

[Akuini]

Why not use the usual replace box (Ctrl+H)?

 

[howard]

Hi Guys


Thanks for the help, much apreciated