VBA - Clear all rows below if

[nitrammada]

Hi Everyone,
I have a macro that clears the row from columns C to P if the cell in column C = 0.
Once the cell in column C is found to contain 0, everything below (including that row) can be cleared in columns C to P.
The macro works but it takes such a long time eg 30 seconds to clear 1500 rows.
Is there a quicker way to do this? Below is what I have already.

Sub ClearErrors()
    'This is for sheet "Print3" to remove the #N/A at bottom of report
Application.ScreenUpdating = False
Application.EnableEvents = False

    Dim Count, i As Long
    Count = ActiveSheet.Cells(Rows.Count, "j").End(xlUp).Row
    'MsgBox count
    i = 8
        Do While i <= Count
            If Cells(i, 3) = 0 Then
                Rows(i).EntireRow.ClearContents
                'Range(Cells(i, 3), Cells(i, 16)) = ""
            End If
        i = i + 1
        Loop
        
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

I have had a look on the forum questions (a big thanks for all those that contribute) but most solutions use the 'Loop' function and it takes ages to clear.
Any suggestions would be most welcome, or perhaps I have something in my code that is slowing it down.
Thanks in advance
Adam

 

[nitrammada]

Try this then, it only made a few seconds difference for me....but...

Sub MM1()
Dim lr As Long, r As Long, t1 As Double, t2 As Double
t1 = Timer
lr = Cells(Rows.Count, "C").End(xlUp).Row
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With
    For r = 8 To lr
        If Cells(r, 3) = 0 Then Range(Cells(r + 1, 3), Cells(lr, 16)).ClearContents
    Next r
With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With
t2 = Timer
MsgBox t2 - t1 & " seconds to run !!"
End Sub

If it works, ALL kudos go to @Alex Blakenburg

It certainly was quicker, 18 secs. I tried on a new workbook without the custom code and it was much quicker, thank you again for your efforts, much appreciated.

 

[nitrammada]

Sorry that should be an

exit do

Offthelip, thank you so much, I just tried it an it was instant. I have had a lot of help from Michael aswell so I am very grateful to everyone involved.

 

[nitrammada]

Sorry that should be an

exit do

Offthelip, thank you so much it worked in an instant, I can't believe it. I had a lot of help from Michael too, so thank you both for your assistance. I am very grateful.

 

[Michael M]

Glad WE could help...