clear contents of range if either of two cells meets a condition

[surlybiker]

In a sample spreadsheet, beginning at row 5, I need to clear the contents of the range E:K if either cell in column G or J is blank or contains a zero.

The following code works, but it is extremely slowly (about a second per row in the actual spreadsheet which contains up to 4000 rows of data across hundreds of columns):

Sub clear_ranges()
Dim ws As Worksheet
Dim x As Integer

Set ws = ThisWorkbook.Sheets("Sheet1")

For x = 5 To ws.Range("A" & Rows.Count).End(xlUp).Row
If (ws.Range("G" & x).Value = 0 Or ws.Range("G" & x).Value = "") And (ws.Range("J" & x).Value = 0 Or ws.Range("J" & x).Value = "") Then
ws.Range("E" & x & ":" & "K" & x).Clear
End If
Next x
End Sub

Is there a more efficient way to perform this task?

Thanks so much!

 

[dk]

Hi

You should get some speed increases by modifying your procedure like so:

Sub clear_ranges()
    Dim ws As Worksheet
    Dim x As Long
    Dim lCalcMode As Long

    'Get the current calculation mode so that it can be set back later
    lCalcMode = Application.Calculation

    'These two lines are standard ways of improving macro performance
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual


    Set ws = ThisWorkbook.Sheets("Sheet1")

    For x = 5 To ws.Range("A" & Rows.Count).End(xlUp).Row
        If (ws.Range("G" & x).Value = 0 Or ws.Range("G" & x).Value = "") And (ws.Range("J" & x).Value = 0 Or ws.Range("J" & x).Value = "") Then
            ws.Range("E" & x & ":" & "K" & x).Clear
        End If
    Next x


    Application.ScreenUpdating = True
    Application.Calculation = lCalcMode


End Sub

Note that I changed the declaration of x from Integer to Long - an integer can only store numbers up to 32,767 which is less than the number of rows on a spreadsheet so you would end up with an overflow error if you have more than this many rows of data. Using a long is always the best way to deal with row numbers.

Please see if this code works any better for you.

HTH
DK

 

[surlybiker]

Fantastic! Thanks so much for the prompt reply. This works perfectly (and quickly).

I appreciate your help!