Delete Range if column A is zero

[koi]

Hi All,

I have a range of data from A3:J100, and i need the code to delete range A3:E100 (can be lastrow as well) if it find zero on column A3:A100

basically something like below, but somehow this code doesnt work, Thanks for helping me

Sub DeleteRange()
Dim r As Long
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For r = LastRow To 1 Step -1
If Cells(r, "A") = 0 Then
Range("A3:E" & LastRow).Delete
End If
Next r
End Sub

 

[jolivanes]

Is this what you had in mind?
Try on a copy of your original first.

Sub AAAAB()
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
    If WorksheetFunction.CountIf(Range("A3:A" & lr), 0) > 0 Then Range("A3:E" & lr).Delete Shift:=xlUp
End Sub

 

[rahulshewale1]

hi @koi,

See if is it ok?

Sub Macrotest()

Dim lrow As Long, r As Long

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

For r = 4 To lrow

 If Range("A" & r).Value = 0 Then
       Range(Range("A" & r), Range("E" & r)).Clear
    End If

Next r

End Sub

 

[Marc L]

Hi,​

​

according to the explanation a VBA beginner demonstration :​

Sub Demo1()
    If IsNumeric(Application.Match(0, [A3:A100], 0)) Then [A3:E100].Clear
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[Marc L]

My demonstration revamped for a moving last row :​

Sub Demo1r()
    With Range("A3", Cells(Rows.Count, 1).End(xlUp))
        If IsNumeric(Application.Match(0, .Value2, 0)) Then .Resize(, 5).Clear
    End With
End Sub

You may Like it !​

​