VBA code taking about 40 minutes to run - why?!

[Nachlawi]

Hi,

I have this code:

    For rw = 2 To num
    Application.StatusBar = String(3, ChrW(9609)) & "Scrubbing at row " & rw & "/" & num & "."
        If InStr(Range("L" & rw).Value, "1") > 0 Then
            Range("A" & rw).Value = "#"
            Range("K" & rw).Value = "#"
            Range("M" & rw).Value = "#"
        End If
        If InStr(Range("L" & rw).Value, "2") > 0 Then
            Range("A" & rw).Value = "#"
            Range("K" & rw).Value = "#"
            Range("M" & rw).Value = "#"
        End If
        If InStr(Range("L" & rw).Value, "3") > 0 Then
            Range("A" & rw).Value = "#"
            Range("K" & rw).Value = "#"
            Range("M" & rw).Value = "#"
        End If
        If InStr(Range("L" & rw).Value, "11") > 0 Then
            Range("A" & rw).Value = "#"
            Range("K" & rw).Value = "#"
            Range("M" & rw).Value = "#"
        End If
        If InStr(Range("L" & rw).Value, "23") > 0 Then
            Range("A" & rw).Value = "#"
            Range("K" & rw).Value = "#"
            Range("M" & rw).Value = "#"
        End If
        If InStr(Range("L" & rw).Value, "39") > 0 Then
            Range("A" & rw).Value = "#"
            Range("K" & rw).Value = "#"
            Range("M" & rw).Value = "#"
        End If


        If Range("E" & rw).Value = "" Then
            Range("E" & rw).ClearContents
        End If
        If Range("F" & rw).Value = "" Then
            Range("F" & rw).ClearContents
        End If
        If Range("G" & rw).Value = "" Then
            Range("G" & rw).ClearContents
        End If
        If Range("H" & rw).Value = "" Then
            Range("H" & rw).ClearContents
        End If
        If Range("I" & rw).Value = "" Then
            Range("I" & rw).ClearContents
        End If
        If Range("J" & rw).Value = "" Then
            Range("J" & rw).ClearContents
        End If
    Next rw

The first part is going through certain cells and scrubbing data if a certain condition is met. The second part is clearing the contents of blank cells so that the COUNT function works properly with those cells. However, this code takes really long to loop through the 300+rows (about 40 mins). Is there a way that I can speed this up

Thanks

 

[tonyyy]

Nachlawi,

You don't show all your code, so I'll ask... Do you have these typical lines at the top and bottom of your code?

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

''''

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

In your "If InStr" statements, it seems there are redundancies. For example...

    If InStr(Range("L" & rw).Value, "1") > 0 Then
        Range("A" & rw).Value = "#"
        Range("K" & rw).Value = "#"
        Range("M" & rw).Value = "#"
    End If

    If InStr(Range("L" & rw).Value, "11") > 0 Then
        Range("A" & rw).Value = "#"
        Range("K" & rw).Value = "#"
        Range("M" & rw).Value = "#"
    End If

You don't need "If InStr(Range("L" & rw).Value, "11") > 0 Then" - since finding a "1" would also find 11.

The same applies to...

    If InStr(Range("L" & rw).Value, "3") > 0 Then
        Range("A" & rw).Value = "#"
        Range("K" & rw).Value = "#"
        Range("M" & rw).Value = "#"
    End If

    If InStr(Range("L" & rw).Value, "23") > 0 Then
        Range("A" & rw).Value = "#"
        Range("K" & rw).Value = "#"
        Range("M" & rw).Value = "#"
    End If
    If InStr(Range("L" & rw).Value, "39") > 0 Then
        Range("A" & rw).Value = "#"
        Range("K" & rw).Value = "#"
        Range("M" & rw).Value = "#"
    End If

...since finding a "3" would also find 23 and 39.

Cheers,

tonyyy

 

[tonyyy]

You can then combine the multiple "If InStr..." statements into a single block...

    If InStr(Range("L" & rw).Value, "1") > 0 Or _
        InStr(Range("L" & rw).Value, "2") > 0 Or _
        InStr(Range("L" & rw).Value, "3") > 0 Then
            Range("A" & rw).Value = "#"
            Range("K" & rw).Value = "#"
            Range("M" & rw).Value = "#"
    End If

As well as reduce the second part of your code to...

    Dim i As Long
    For i = 5 To 10
        If Cells(rw, i).Value = "" Then Cells(rw, i).ClearContents
    Next i

 

[Rick Rothstein]

You don't need "If InStr(Range("L" & rw).Value, "11") > 0 Then" - since finding a "1" would also find 11.

The same applies to...

    If InStr(Range("L" & rw).Value, "3") > 0 Then
        Range("A" & rw).Value = "#"
        Range("K" & rw).Value = "#"
        Range("M" & rw).Value = "#"
    End If

    If InStr(Range("L" & rw).Value, "23") > 0 Then
        Range("A" & rw).Value = "#"
        Range("K" & rw).Value = "#"
        Range("M" & rw).Value = "#"
    End If
    If InStr(Range("L" & rw).Value, "39") > 0 Then
        Range("A" & rw).Value = "#"
        Range("K" & rw).Value = "#"
        Range("M" & rw).Value = "#"
    End If

...since finding a "3" would also find 23 and 39.

You raise an interesting point... I wonder if the OP's code is doing what he intended it to do. What I mean is... did he really want to isolate any cell with a 1, 2, 3 in it (for example, 17, 92, 32 and such) or was he looking for specifically the numbers 1, 2, 3 by themselves?

 

[tonyyy]

I wonder if the OP's code is doing what he intended it to do.

I always assume posted code works as intended!

If the OP is looking for a specific value, then why use InStr?

 

[Rick Rothstein]

I always assume posted code works as intended!

I usually do as well, but sometimes things don't make sense to me and then I'll question the poster before spending time trying to develop a solution for what I think may be the wrong problem.

If the OP is looking for a specific value, then why use InStr?

Just the fact that the OP specified 1, 2 and 3 initially and then specific numbers that contained 1, 2 and 3 just seemed like he might have used the wrong If..Then test for what he actually was trying to do.

 

[Norie]

How is the code being executed?

Do you have any other code that this code could be triggering, e.g. sheet event code?

 

[Nachlawi]

Just the fact that the OP specified 1, 2 and 3 initially and then specific numbers that contained 1, 2 and 3 just seemed like he might have used the wrong If..Then test for what he actually was trying to do.

Yes, so I did change the values of some of my code in order to post it. And I've now realised that my code wouldn't work as it does with the actual values before I made the changes. The values that I use should be unique.

 

[Nachlawi]

Do you have these typical lines at the top and bottom of your code?

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

''''

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

I have the screenupdating one, but the calculation one produced problematic results when I used it a while back, although I can't remember what exactly the problem was.

 

[igold]

@Nachlawi

As inefficient as the code is as pasted (although there is nothing horrible about it), if it is taking 40 minutes to run through ~300 rows of data, something else is definitely going on. You might want to post the entire code perhaps some sample data. You have a lot of good people following this thread, you need to help them help you.