Suddenly Slow Code Execution

MatthewM · #1 (**permalink**) 04-20-11, 23:40

Hey guys and girls,

I have a problem with slow code execution on a spreadsheet I use quite frequently.

The code is simple, all it does is run through a table of data looking for specific criteria to be true in each Row (an autofilter essentially) then populate a table with positive matches. Thanks to JerryDal for this code

you showed me how to do it initially.

Here it is:

Code:

Sub RFS_populate()
Dim xx As Integer, bb As Integer, mm As Integer
Application.Calculation = xlCalculationManual

xx = 6
bb = 4
mm = 2

code = "RFS" & Cells(2, 3).Value & Cells(1, 3).Value

Do While Cells(xx, 2).Value <> ""
    Cells(xx, 2).Value = ""
    Cells(xx, 3).Value = ""
    Cells(xx, 4).Value = ""
    Cells(xx, 5).Value = ""
    Cells(xx, 6).Value = ""
    Cells(xx, 7).Value = ""
    Cells(xx, 8).Value = ""
    Cells(xx, 9).Value = ""
    xx = xx + 1
Loop
 
mm = 6
 
Do While Sheets("Inspections").Cells(bb, 8).Value <> ""
    codechk = Sheets("Inspections").Cells(bb, 3).Value & Sheets("Inspections").Cells(bb, 10).Value & Sheets("Inspections").Cells(bb, 9).Value
    If codechk = code Then
        Cells(mm, 2).Value = Sheets("Inspections").Cells(bb, 5).Value
        Cells(mm, 3).Value = Sheets("Inspections").Cells(bb, 2).Value
        Cells(mm, 4).Value = Sheets("Inspections").Cells(bb, 8).Value
        Cells(mm, 5).Value = Sheets("Inspections").Cells(bb, 11).Value
        Cells(mm, 6).Value = Sheets("Inspections").Cells(bb, 12).Value
        Cells(mm, 7).Value = Sheets("Inspections").Cells(bb, 13).Value
        Cells(mm, 8).Value = Sheets("Inspections").Cells(bb, 14).Value
        Cells(mm, 9).Value = Sheets("Inspections").Cells(bb, 15).Value
        mm = mm + 1
    End If

bb = bb + 1

Loop
Application.Calculation = xlCalculationAutomatic
        
End Sub

Now, normally this code executes almost instantly, However, in adding another table that is populated using the same information and SUMPRODUCT my code is immediately slowed to a crawl. I can speed it up by quickly clicking the mouse anywhere on the sheet (which is wierd right?).

Attached is the added worksheet, please excuse me not putting the file up completely as i cannot.

Colin Legg · #2 (**permalink**) 04-21-11, 14:42

Hi Matt,

I can't view your attachment?

MatthewM · #3 (**permalink**) 09-26-11, 00:28

Sorry I've been meaning to tie this one up for a while, I no longer have the file but I did eventually work out why it was doing this.

Unfortunately I can't for the life of me remember how but essentially the problem was down to inefficent code.

Very sorry for the loose end here.

Internet Services

Web Development

Digital Marketing

Consumer Tech