Inserting Multiple Rows in Excel is very slow

Question

The code is working but it is taking more time to insert rows.

I normally insert more than 500 line items. It is updating one by one.

How can I speed up the macro?

Sub InsertMultipleRows()

Dim numRows As Integer
Dim counter As Integer

'Select the current row
ActiveSheet.Unprotect
Range("C23").Select
ActiveCell.EntireRow.Select
On Error GoTo Last
numRows = InputBox("Enter number of rows to insert", "Insert Rows")

'Keep on inserting rows until we reach the desired number
For counter = 1 To numRows
    Selection.Insert Shift:=xlToDown, CopyOrigin:=xlFormatFromRightorAbove
Next counter
Last: Exit Sub
End Sub

Dont insert them one by one... insert them all at once. or at least turn off calculation and screenupdating. — braX, Dec 16 '19 at 06:26
So many learning opportunities, so many issues that are covered numerous times on this site: `Activate` & `Select` (Avoid Select), an error handler that does nothing, getting an integer from an InputBox which returns as "string", no input validation to make sure an integer is entered (what if the user enters "Blah"?). And then there is the issue @braX noted, inserting the full range in the one hit. — AJD, Dec 16 '19 at 06:32
http://stackoverflow.com/questions/29596432/pointers-needed-for-speeding-up-nested-loop-macro-in-vba/29597193#29597193 — , Dec 16 '19 at 06:39

Siddharth Rout · Answer 1 · 2019-12-16T07:30:55.140

The code that you are using is an inefficient way of inserting rows.

Try this one liner

Range("C23").EntireRow.Resize(numRows).Insert

Couple of other things

Avoid using Integer when working with rows. Use Long to avoid possible Overflow error.
Avoid the use of .Select. You may want to see How to avoid using Select in Excel VBA
If you are using an Inputbox to accept number then as @AJD suggested, do a proper validation. OR better still, use Application.InputBox method (Excel) with Type:=1.

Inserting Multiple Rows in Excel is very slow

1 Answers1