Slow Performance When Running Some VBA Code

[mobiius]

I have created a spreadsheet (1) which opens an external workbook (2), copies the contents of several specific worksheets in workbook (2), and pastes them into the first spreadsheet (1) into a single sheet.

Then a userform pops up and the user is given a choice of two reports to run on the data which was copied over.

When the first option is selected, the code will basically scan column B and search for the abbreviation NA, if it finds it, it skips over it. If it doesn't equal NA, it deletes the entire row so that when it is done only those rows that contain NA in column B remain.

There are 2343 rows that it must scan through. The problem right now is that it takes 3 seconds on each line. It'll take almost 2 hours to run through it all! It would be great if it could run through it in a couple fo minutes...

Is there an inefficiency in my code or is Excel just not able to quickly do this sort of thing? I've tried it with about 40 rows of data and it seems to run faster. How can I improve the speed? Is there a better way to do this?

Here's the code I use:

Private Sub cmdRunReport_Click()
    Dim Target As String
    If UserForm1.cboReportType.ListIndex = -1 Then
        MsgBox "You must first select a report type.", vbOKOnly
    Exit Sub
    End If
    
    Sheets("Sheet1").Select
    Range("B1").Select
    
    If UserForm1.cboReportType.ListIndex = 0 Then
        Target = "NA"
        Do Until ActiveCell.Value = ""
        If ActiveCell.Value <> Target Then
        ActiveCell.EntireRow.Delete
        Else
        ActiveCell.Offset(1, 0).Activate
        End If
        Loop
    End If
    
    If UserForm1.cboReportType.ListIndex = 1 Then
        Target = "FA"
        Do Until ActiveCell.Value = ""
        If ActiveCell.Value <> Target Then
        ActiveCell.EntireRow.Delete
        Else
        ActiveCell.Offset(1, 0).Activate
        End If
        Loop
    End If
    
End Sub

Thanks!

 

[JamesW]

Hi there,

Do you have formulas on the sheet that are recalculating when you have made changes?

You could maybe try these at the start:

Application.Screenupdating = False
Application.Calculation = xlCalculationManual

And then these at the end:

Application.Screenupdating = True
Application.Calculation = xlCalculationAutomatic

 

[baitmaster]

Couple of suggestions...

When you delete a row, Excel "animates" it, you can change this somewhere in your options, it might speed things up a bit

Switch off calculations and switch them back on afterwards:
application.Calculation = (xlCalculationAutomatic / xlCalculationManual)

You could use autofilter to quickly hide the sections you dont want, then use range("your_range_name").SpecialCells xlCellTypeVisible to select only those cells that remain visible

This is a very commonly asked question, keep looking around this site for more answers

 

[mobiius]

Wow! That did the trick BEAUTIFULLY!
It ran the report in about 5 seconds!

Thanks for the awesome help James!

 

[mobiius]

Thanks Baitmaster, looks like James beat you to it, but I appreciate the help all the same!

 

[baitmaster]

Thanks Baitmaster, looks like James beat you to it

d'oh!

I don't care what anyone says, this IS a competition!