Speeding up dealing with large volumes in Excel VBA

Question

I am using vba to get lists of the different accounts with different currencies. To do this I am using a normal filter to select the currency and then an advanced filter to pick out all the accounts for that currency. It then pastes the list on a sheet called Accts.

The spreadsheet has over 30,000 lines of info and is very slow is there anyway of doing it faster. My code is below. I think it works but just takes forever.

Sub Filtering()


Application.ScreenUpdating = False

intLastRow = Worksheets("report").Cells(Rows.Count, "b").End(xlUp).Row

intLastCol = Worksheets("info sheet").Cells(Columns.Count, 7).Column

Set rngAdvFilter = Worksheets("report").Range("b7:m" & intLastRow)
Set rngCriteria = Worksheets("report").Range("d7:d" & intLastRow)

Set rRange = Worksheets("info sheet").Range("c7:m7")

For Each rCell In rRange


strCurrency = rCell.Value

With rngAdvFilter
  .AutoFilter Field:=6, Criteria1:= _
    "=" & strCurrency, Operator:=xlAnd 'filtering on currency so we are
    'looking for all accounts on a certain currency
    End With


Worksheets("accts").Select

Range("b1:aa1").Select



Selection.find(What:=strCurrency, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate

    Set rngPaste = ActiveCell.Offset(1, 0)


    rngPaste.Select

Worksheets("report").Select

Range("D7:D" & intLastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
    "P7"), Unique:=True



intLastRow2 = Worksheets("report").Cells(Rows.Count, "p").End(xlUp).Row

Set rngResults = Worksheets("report").Range("P8:P" & intLastRow2)

rngResults.Copy



rngPaste.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

rngResults.ClearContents


If Worksheets("report").FilterMode Then

Worksheets("report").ShowAllData

End If


Next rCell

Application.ScreenUpdating = True

End Sub

Hard to understand your data structure or exactly what you want for output. If LeasMaps recommendation doesn't work, I would suggest you edit your question with examples of your data and desired output (or, better, post a link to a workbook we can download). There are all kinds of ways to speed up the process. — Ron Rosenfeld, Jun 30 '14 at 00:43
having hard time decoding your recorded macro with so little comments. But your `intLastCol = Worksheets("info sheet").Cells(Columns.Count, 7).Column` is effectively `intLastCol = 7` If you are to look at last column of row 7: `intLastCol = Worksheets("info sheet").Cells(7, Columns.Count).End(xlToLeft).Column` — PatricK, Jul 01 '14 at 00:43

score 1 · Answer 1 · edited May 23 '17 at 10:32

1

I think you may have to do a bit of re-writing. The selection of each cell is what is really slowing things down. I'd suggest starting here How to avoid using Select in Excel VBA macros

I would also recommend declaring all variables ( put "Option Explicit" at the top of your module as well) Lea.

edited May 23 '17 at 10:32

Community

1
1

answered Jun 29 '14 at 08:23

LeasMaps

300
4
14

Thanks will look at that. Have declared all my variables and put option explicit. – Louisa Thompson Jun 29 '14 at 08:31

score 1 · Accepted Answer · answered Jun 30 '14 at 04:04

This is definitely going to run very slowly. Each time you access the spreadsheet (Range access) it's going to slow things down. The way to go about doing this is executing your code by accessing the sheet only twice. Once to fetch the data and the other time to put the required stuff back on the sheet.

e.g.

Sub ProcessData()
    Dim Rng as Range, OutputRng as Range
    Set Rng = Worksheets("Sheet1").Range("A1:D20000")
    ' Spreadsheet Access # 1
    ' Assuming you want to do something to the data in that range

    Dim InputDat as Variant, OutputDat() as Variant
    InputDat = Rng
    ' Now InputDat has become a Variant array of size 20000 x 4

    ' Code to manipulate Dat goes here        

    Redim OutputDat(1 to 100, 1 to 4)    'Or dynamically as needed
    ' Put required output data into OutputDat as if it's the range
    ' where your answers appear following the correct row/column number

    Set OutputRng = Worksheets("Sheet2").Range("A1:D100")
    OutputRng = OutputDat
    ' Spreadsheet access #2
End Sub

This would speed things up by a few orders of magnitude. Also, the usual Application.Screenupdating, etc. might help depending on what else you do in the macro, but considering there's not much happening on screen, with only 2 accesses, this should be mighty fast.

This may be a bit beyond my VBA understanding but will look into it. Thank you. I basically need to produce a list of accounts with each currency and paste them to a new sheet to see if they are already included. There will be formulas there to check if the acct is on the sheet. — Louisa Thompson, Jul 01 '14 at 13:37
Yes, while this is slightly more complicated than directly accessing the ranges, it would be much faster since you're fetching the entire range in one stroke, working on it, and pumping it back into the sheet in one more stroke. You could start with first simply fetching the values using a variant array as shown, and then slowly modifying your code piece by piece. The beauty of this is that the rest of your code (other functions, macros) can stay as it is as all the speed-up takes place internally. — hnk, Jul 01 '14 at 16:54

Speeding up dealing with large volumes in Excel VBA

2 Answers2