Code to insert n blank cells after every row is very slow

Question

This would be my code, but it takes a lifetime if I have over 5000 rows, and 1 column. Is there a way to reduce the query time?

Sub InsertRows() 
    Dim numRows As Integer 
    Dim r As Long 

    Application.ScreenUpdating = False 

    r = Cells(Rows.Count, "A").End(xlUp).Row 
    numRows = 11 

    For r = r To 1 Step -1 
        ActiveSheet.Rows(r + 1).Resize(numRows).Insert 
    Next r 

    Application.ScreenUpdating = True 
End Sub

Takes about 25 sec for me. Quite normal for the operation. Try using `.Insert xlShiftDown`, but that won't matter much anyway. — GSerg, Jul 19 '12 at 11:36
If you can't *effectively* reduce the time, you can make it *appear* shorter with a progressbar: http://stackoverflow.com/questions/5181164/progress-bar-in-vba-excel — JMax, Jul 19 '12 at 12:26

score 6 · Accepted Answer · answered Jul 19 '12 at 12:53

This took < 1 second.

Sub InsertRows()

Dim numberOfValues As Long
Dim i As Long
Dim values As Variant

Const numberOfEmptyRows As Long = 11

Application.ScreenUpdating = False

' count values in column A
numberOfValues = Cells(Rows.Count, "A").End(xlUp).Row

' populate array with numbers
ReDim values(1 To numberOfValues, 1 To 1)
For i = 1 To numberOfValues
  values(i, 1) = i
Next i

' I know there is a better way to do this part...
Range(Cells(1, 2), Cells(numberOfValues, 2)).Value = values
For i = 1 To numberOfEmptyRows - 1
  Range(Cells(Rows.Count, "B").End(xlUp).Offset(1, 0), Cells(Rows.Count, "B").End(xlUp).Offset(numberOfValues, 0)).Value = values
Next i

' sort by values inserted in column B
Range(Cells(1, 1), Cells(Rows.Count, "B").End(xlUp)).Sort Key1:=Range("B1"), _
        Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

Columns("B:B").EntireColumn.Delete

Application.ScreenUpdating = True
End Sub

This code uses a helper column (in this case, B) to insert a number sequence next to the target range. Then it adds the same numbers N times below itself, and sorts on that column. Finally, it deletes the column. This is how you can quickly insert blank rows into any data set.

Change Const numberOfEmptyRows As Long = 11 if you want to insert more/less blank rows. There is a limit to how many records this technique can handle (and how many blank rows can be inserted) before you hit Excel's row limit.

Awesome. I have had similar issues with the slowness of excel updating the display. Good to know I can simply set the updating to false. — Bryan, Jul 19 '12 at 13:00

score 0 · Answer 2 · edited Jun 19 '13 at 13:48

0

Jimmy, your code is very nice. But if there isn't any content in 'A' column while the other columns have some the result of sorting will go wrong.

So, I will use UsedRange to get numberOfValues as below.

 Set r2Arrange = ActiveSheet.UsedRange

count values in column A:

numberOfValues = r2Arrange.Rows.Count

edited Jun 19 '13 at 13:48

Daniel Fath

16,453
7
47
82

answered Jun 19 '13 at 13:17

user2501310

1

Code to insert n blank cells after every row is very slow

2 Answers2