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

 

[offthelip]

this small change will speed it up a bit:

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
    inarr = Range(Cells(1, 3), Cells(Count, 3))
    'MsgBox count
    i = 8
        Do While i <= Count
            If inarr(i, 1) = 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

However if your worksheet doesn't contain formula then you could do all of it using variant arrays, which would be very very fast

    Dim Count, i As Long

Defines count as variant is this what you intended?? It will still work!!

 

[nitrammada]

this small change will speed it up a bit:

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
    inarr = Range(Cells(1, 3), Cells(Count, 3))
    'MsgBox count
    i = 8
        Do While i <= Count
            If inarr(i, 1) = 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

However if your worksheet doesn't contain formula then you could do all of it using variant arrays, which would be very very fast

    Dim Count, i As Long

Defines count as variant is this what you intended?? It will still work!!

Hello offthelip, thanks so much for responding.
I added your suggestion to the code and it works, thank you, but still takes about 30 seconds to clear 1500 rows. Is this normal, perhaps I am expecting too much.
I'm sorry but I don't know what 'define count as variant' really means, I just know it works as I have copied code from this forum to solve many of my VBA problems. The columns I am clearing all have Xlookup formula in them and they are all referencing column C, so that when a cell in column C finally returns a 0 from that row down all the xlookup formulas can be deleted. Any thoughts?

 

[Michael M]

Maybe this way

Sub MM1()
Dim lr As Long, r As Long
lr=Cells(Rows.Count, "C").End(xlUp).Row
Application.ScreenUpdating = False
    For r = 1 to lr 
        If Cells(r, 3) = 0 Then Range(Cells(r, 3), Cells(lr, 16)).Clear
    Next r
Application.ScreenUpdating = True
End Sub

 

[offthelip]

I am not sure a entirely understand what you are trying to do, but if you want to clear the worksheet from the first occurance of 0 in column C then this code will be really fast:

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
    inarr = Range(Cells(1, 3), Cells(Count, 3))
    'MsgBox count
    i = 8
        Do While i <= Count
            If inarr(i, 1) = 0 Then
                 Exit For
                'Rows(i).EntireRow.ClearContents
                'Range(Cells(i, 3), Cells(i, 16)) = ""
            End If
        i = i + 1
        Loop
Range(Cells(i, 1), Cells(Count, 1)).EntireRow.ClearContents
      
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[nitrammada]

I am not sure a entirely understand what you are trying to do, but if you want to clear the worksheet from the first occurance of 0 in column C then this code will be really fast:

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
    inarr = Range(Cells(1, 3), Cells(Count, 3))
    'MsgBox count
    i = 8
        Do While i <= Count
            If inarr(i, 1) = 0 Then
                 Exit For
                'Rows(i).EntireRow.ClearContents
                'Range(Cells(i, 3), Cells(i, 16)) = ""
            End If
        i = i + 1
        Loop
Range(Cells(i, 1), Cells(Count, 1)).EntireRow.ClearContents
     
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Hello Offthelip,
Thanks again for your assistance, I get a compile error message 'Exit For not within For....Next' Any thoughts what this might be?
Kind regards

 

[Michael M]

Did post#4 not do as required??

 

[nitrammada]

Maybe this way

Sub MM1()
Dim lr As Long, r As Long
lr=Cells(Rows.Count, "C").End(xlUp).Row
Application.ScreenUpdating = False
    For r = 1 to lr
        If Cells(r, 3) = 0 Then Range(Cells(r, 3), Cells(lr, 16)).Clear
    Next r
Application.ScreenUpdating = True
End Sub

Hello Michael,
Thank you for you suggestion, it works brilliantly, but it clears all my data. I have about 1000 rows of valid data generated by a number in column C. Eventually the number in column C returns a 0 zero number. From this point on, downwards, columns D to P is populated with a series of blanks, #Ref or #N/A etc, its this data I would like to clear. Is it possible to adjust your code so that if a zero is found in column C then clear from that point down everthing that is in columns C to P?
Thanks in advance.
Regards
Adam

 

[Michael M]

Slight change

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "C").End(xlUp).Row
Application.ScreenUpdating = False
    For r = 1 To lr
        If Cells(r, 3) = 0 Then Range(Cells(r + 1, 3), Cells(lr, 16)).Clear
    Next r
Application.ScreenUpdating = True
End Sub

The code provided clears columns C to P, 1 row after the found "0" through to the last row. Isn't that what you needed ??

 

[nitrammada]

Did post#4 not do as required??

It came up with an error, I have asked Offthelip to see if he knew what the error was.