VBA - How to make For loop faster

Question

This loop takes about 2 minutes for 15 worksheets with each less than a hundred rows. Is there a faster way to do this?

For Each ws In Worksheets
    For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
        ws.Cells(i, "H").Value = ws.Cells(i, "C").Value & ws.Cells(i, "A").Value
    Next i
Next

**Note** you're not qualifying `Cells(Rows.Count, "A").End(xlUp).Row`. This could lead to logical bugs in your code that are difficult to pinpoint. Always remember to qualify your ranges `ws.Cells(Rows.Count, "A").End(xlUp).Row` — Super Symmetry, Aug 18 '20 at 09:51
Maybe it is enought to do [this](https://stackoverflow.com/questions/47089741/how-to-speed-up-vba-code/47092175#47092175) — Storax, Aug 18 '20 at 09:51
I note that with a new workbook, setting up data of 100 rows in each of 15 worksheets, your code takes less than one second to run on an old computer. Disabling stuff as suggested by @Storax should help considerably. — Ron Rosenfeld, Aug 18 '20 at 12:00
My experience is that by disabling screen updates (in some cases I even set application visibility to false) and automatic calculation, the execution is fastest. Also avoid debug output unless you are not sure if your code is running properly. Downside is that for macros running for several minutes you have no indication if there is any progress or if the macro is stuck in an infinite loop. — Ultra Junkie, Aug 18 '20 at 12:37

Super Symmetry · Answer 1 · 2020-08-18T09:53:53.443

3

Try this (not tested)

For Each ws In Worksheets
    With ws.Range("H2").Resize(ws.Cells(Rows.Count, "A").End(xlUp).Row - 1, 1)
        .Formula = "=C2&A2"
        .Value = .Value
    End With
Next

Also consider adding

Application.ScreenUpdating = False
Application.EnableEvents = False

to the beginning of your code and reset them to True at the end just in case you're triggering code in event callbacks.

edited Aug 18 '20 at 09:53

answered Aug 18 '20 at 09:44

Super Symmetry

2,837
1
6
17

score 3 · Answer 2 · answered Aug 18 '20 at 09:58

Another approach could be to copy the data into arrays and write the arrays back but I am not sure if this is really faster than the appoach with formulas and then replacing the formulas with the values

Sub TestIt()
Dim i As Long
Dim ws As Worksheet
Dim aDat As Variant, cdat As Variant, hDat As Variant, lastRow As Long

    TurnOff
    For Each ws In Worksheets
        With ws
            lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            aDat = .Range("A2:A" & lastRow).Value2
            cdat = .Range("C2:C" & lastRow).Value2
            ReDim hDat(1 To lastRow, 1 To 1)
            For i = LBound(aDat) To UBound(aDat)
                hDat(i, 1) = aDat(i, 1) & cdat(i, 1)
            Next i
            .Range("H2:H" & lastRow).Value2 = hDat
    
        End With
    Next
    TurnOn
End Sub

Sub TurnOff()
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
End Sub
Sub TurnOn()
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
End Sub

I don't think in this case the array apporach would gain any speed... If there are only hundreds of rows... A side note: you can just write a single function `MemorySave(isOn As Boolean)` and then you do this for calculation: `Application.Calculation = IIf(isOn, xlCalculationManual, xlCalculationAutomatic)` and `Application.ScreenUpdating = Not (isOn)` for the rest. `SaveMemory True` will turn off everything, and `SaveMemory False` will reset everything. — Damian, Aug 18 '20 at 10:03
Yes, you are possibly right that the OP will not gain any speed with the array approach for hundred or even thousand rows. I just wanted to show it to the OP as an alternative. Regarding your side note: Normally one would save the previous state of `Calculation, Sreenupdating` etc. and then reset it to the save states. But this is not really neccessary here IMO. Maybe automatic recalculation is the speed killer for OP's code anyway. — Storax, Aug 18 '20 at 10:18
The array approach generally runs affords at least a 10-fold improvement in speed. But in this case, the absolute difference (after disabling calculations, events, etc) shouldn't be much. — Ron Rosenfeld, Aug 18 '20 at 12:08

score 0 · Answer 3 · answered Aug 18 '20 at 12:07

I hope this may help you to execute the macro faster.

Sub Macro1()

Dim ws As Worksheet, rCount As Integer, rng As Range

For Each ws In Worksheets
    rCount = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    ws.Range("H2").Formula = "=CONCATENATE(" & ws.Range("C2").Address(RowAbsolute:=False) & "," & ws.Range("A2").Address(RowAbsolute:=False) & ")"
    Set rng = ws.Range("H2:H" & rCount)
    ws.Range("H2").Copy
    rng.Select
    ws.Paste
    rng.Copy
    rng.PasteSpecial (xlPasteValues)
    Application.CutCopyMode = False
Next

End Sub

Thanks, Ramana

VBA - How to make For loop faster

3 Answers3