Replace This While Loop with More Efficient Code?

Question

The below code copies the named range "MasterLLTable" n times where n is the difference between the two integers NumTables and UserNumTables. n can be in the hundreds, so the loop can take many seconds to run. Is there a way to speed this operation up by replacing the While loop with something more efficient? Need to make n copies of "MasterLLTable" without the While loop if possible. Also, the spacing between the tables must be preserved.

Do While NumTables < UserNumTables                   
    Range("MasterLLTable").Copy EIRPLL.Cells(LastLLRow + 2, 2)
    LastLLRow = EIRPLL.UsedRange.Rows.Count
    NumTables = (EIRPLL.UsedRange.Rows.Count - 4) / 15
Loop

Answer 1

That code is fairly optimal, there are things in it you could nitpick, however that is most likely NOT what is causing the slow execution you see. VBA is inherently slow because of all the interop that takes place. However, you can speed it up with a few tricks:

• First, set Application.Calculation = xlCalculationManual before executing the loop, then Application.Calculation = xlCalculationAutomatic after the loop executes. This will stop excel from calculating each and every cell after each paste operation.

• Second, you can set Application.ScreenUpdating = FALSE around the same time you set Application.Calculation, this will stop the GUI from updating anything while the script is running. Once the loop is complete (or the script), set Application.ScreenUpdating = TRUE to let Excel start doing its thing again.

I have noticed that these two things tend to make the largest difference in performance of a VBA script. There is a website Optimizing VBA that goes through some other things you can do, but they may not be worth your time. There is more discussion on the subject on another question I answered: VBA - Code Execution is Extremely Slow