Insert blank rows in your data

In Code to insert n blank cells after every row is very slow I posted a method for inserting blank rows in between data. We can take that code and make it more generic so it will act on any data range.

Of course, we still have to make some assumptions, but who doesn't these days?

The following code takes a contiguous range of data and inserts N blank rows between each existing row.

Sub InsertRows()

  Dim numberOfValues As Long
  Dim i As Long
  Dim values As Variant
  Dim rightmostColumn As Excel.Range

  ' change this to indicate how many blank rows you want to insert
  Const numberOfEmptyRowsToAdd As Long = 2

  Application.ScreenUpdating = False

  ' count values in column A
  ' assume column A is filled down to the bottom of the last used row
  ' with row 1 as header
  numberOfValues = Cells(Rows.Count, "A").End(xlUp).Row - 1

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

  ' programmatically determine the rightmost column
  ' to use as a helper column
  Set rightmostColumn = Cells(1, 1).End(xlToRight).Offset(, 1).End(xlDown)
 
  ' paste array values into helper column
  For i = 1 To numberOfEmptyRowsToAdd + 1
    Range(rightmostColumn.End(xlUp).Offset(1, 0), _
      rightmostColumn.End(xlUp).Offset(numberOfValues)).Value = values
  Next i

  ' sort by helper column to "insert" blank rows
  Range(Cells(1, 1), rightmostColumn.End(xlUp)).Sort Key1:=Range("H1"), _
                                              Order1:=xlAscending, Header:=xlYes, _
                                              OrderCustom:=1, MatchCase:=False, _
                                              Orientation:=xlTopToBottom, _
                                              DataOption1:=xlSortNormal

  ' remove helper column to clean up
  Columns(rightmostColumn.Column).EntireColumn.Delete

  Application.ScreenUpdating = True
End Sub

What It Does

This code uses a well-documented technique for inserting blank rows: Insert Alternate Blank Rows in Excel. We merely automate the process using VBA.

It assumes that you have a range of values starting in column A (although we probably could have calculated this as well, I got lazy), with row 1 as the header.

First we count the number of values/rows (minus 1 of course because we don't want to sort the header into the data). Then we populate an array with ordinal values up to the number of values/rows.

Then we figure out the rightmost column, to use as a helper column for pasting in the array. The array is pasted once next to the data, then N times below it (one set of numbers for each blank row we want to insert). We sort the blank rows into our data set, then remove the helper column to tidy up.

Before

After

Change the constant value to insert more or less rows. Be aware that for ranges with large amounts of rows, and where you want to insert a large number of blank rows, you may run out of rows. For example, if you have 10,000 rows of data and want to insert 10 blank rows, you need a workspace of 100,000 rows.

I recommend you test this on a copy of your worksheet before using it on the real thing.