Macro 35: Inserting Blank Rows in a Range
Occasionally, you may need to dynamically insert rows into your dataset. Although blank rows are
generally bothersome, in some situations, the final formatted version of your report requires them to
separate data. The macro in this section adds blank rows into a range.
How it works
This macro performs a reverse loop through the chosen range using a counter. It starts at the last row of
the range inserting two blank rows, and then moves to the previous row in the range. It keeps doing that
same insert for every loop, each time incrementing the counter to the previous row.
Sub Macro35()
‘Step1: Declare your variables.
Dim MyRange As Range
Dim iCounter As Long
‘Step 2: Define the target Range.
Set MyRange = Range(“C6:D17”)
‘Step 3: Start reverse looping through the range.
For iCounter = MyRange.Rows.Count To 2 Step -1
‘Step 4: Insert two blank rows.
MyRange.Rows(iCounter).EntireRow.Insert
MyRange.Rows(iCounter).EntireRow.Insert
‘Step 5: Increment the counter down
Next iCounter
End Sub
1. We first declare two variables. The first variable is an object variable called MyRange. This is
an object variable that defines the target range. The other variable is a Long Integer
variable called iCounter. This variable serves as an incremental counter.
2. In Step 2, the macro fills the MyRange variable with the target range. In this example, we are
using Range(“C6:D17”). If your target range is a named range, you could simply enter its
name — Range(“MyNamedRange”).
3. In this step, the macro sets the parameters for the incremental counter to start at the max
count for the range (MyRange.Rows.Count) and end at 2 (the second row of the chosen
range). Note that we are using the Step-1 qualifier. Because we specify Step -1, Excel
knows we are going to increment the counter backwards, moving back one increment on
each iteration. In all, Step 3 tells Excel to start at the last row of the chosen range, moving
backward until it gets to the second row of the range.
4. When working with a range, you can explicitly call out a specific row in the range by passing a
row index number to the Rows collection of the range. For instance, Range(“D6:D17”).
Rows(5) points to the fifth row in the range D6:D17.
In Step 4, the macro uses the iCounter variable as an index number for the Rows collec-
tion of MyRange. This helps pinpoint which exact row the macro is working with in the cur-
rent loop. The macro then uses the EntireRow.Insert method to insert a new blank
row. Because we want two blank rows, we do this twice.
5. In Step 5, the macro loops back to increment the counter down.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
