0

I built an add-in that matches descriptions to a word list library in a different document.

My loop does the same thing over and over, but one loop takes just over a second to complete where 10 takes around 3 seconds per iteration, with 20 it goes to an average of 4 seconds, but 200 takes hours to complete.

A snippet of the code:

For Each rRow In CoAsh.Range(matchRange.Address).Rows
    pctDone = i / rowCount

    With frmProgress
        .LabelCaption.Caption = "Processing account " & i & " of " & rowCount
        .LabelProgress.Width = pctDone * (.FrameProgress.Width)
        .LabelPercent = Round(pctDone * 100, 0) & "%"
    End With
    DoEvents

    accArray = sh.Range("A2:A" & lRow).Value

    For b = LBound(accArray) To UBound(accArray)
        accString = accArray(b, 1)
        sh.Cells(b + 1, 3).Value = levenshtein(CoAsh.Cells(rRow.Row, AccCol), accString, True)
    Next b

    sh.Select
    sh.AutoFilter.Sort.SortFields.Clear
    sh.AutoFilter.Sort.SortFields.Add Key:=Range("C1:C" & lRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortTextAsNumbers
    With sh.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    sh.Range("B2:B6").Copy
    sh.Range("I2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    sh.Range("$I$2:$I$6").RemoveDuplicates Columns:=1, Header:=xlNo
    sh.Range("I2:I6").Copy
    CoAsh.Select
    CoAsh.Cells(rRow.Row, 8).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

    i = i + 1

Next rRow
Community
  • 1
  • 1
Renier Wessels
  • 131
  • 11
  • 1
    1. Avoid using `.Select`, read [here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). 2. If you don't really need it - try removing `Do Events` from inside of the loop - it pauses macro to do some excel stuff (yes, it's a very general description of "Do Events"). 3. Use `Application.ScreenUpdating = False` method before the loop and `Application.ScreenUpdating = True` after (don't see it in this part of code). – Vitaliy Prushak Jan 09 '20 at 08:39
  • Thank you. I'll try and implement some of your suggestions and see what difference it makes. I appreciate your quick feedback. – Renier Wessels Jan 09 '20 at 08:41
  • 1
    try to avoid using iteration through excel cells.. I ve tested it, and loop over dictionary is the fastest.. (faster than array also) – Peter Ksenak Jan 09 '20 at 08:42
  • As this is working code, then you should post on the Code Review Stack. – Solar Mike Jan 09 '20 at 08:46
  • Thanks for your suggestions everyone. I ended up writing both sources into an array first and then looping only the arrays. That's the only thing that improved overall time in the end. – Renier Wessels Jan 20 '20 at 10:37

0 Answers0