Insert Blank Rows Between Existing Rows In Excel VBA

Sometimes, you may need to insert blank rows between each of the existing rows into your Worksheet. Although blank rows are generally bothersome, in some situations, the final formatted version of your report requires them to separate data. This simple macro will help you to do this. If you don't want to use VBA to do this, see this tip.

VBA Code

Sub InsertBlankRows()
'Step1:  Declare your variables.
    Dim MyRange As Range
    Dim iCounter As Long
'Step 2:  Define the target Range.
    Set MyRange = Range("A2:C8")
'Step 3:  Start reverse looping through the range.
    For iCounter = MyRange.Rows.Count To 2 Step -1
'Step 4: Insert two blank rows.

'Step 5: Increment the counter down
    Next iCounter
End Sub

How This Macro Works

This macro performs a reverse loop through the chosen range using a counter. It starts at the last row of the range inserting one blank rows, and then moves to the previous row in the range. It keeps doing that same insert for every loop, each time increment the counter to the previous row.

  1. In first Step, 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(“A2:C8”). 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(“A2:C8”).Rows(4) points to the forth row in the range A2:C8. In Step 4, the macro uses the iCounter variable as an index number for the Rows collection of MyRange. This helps pinpoint which exact row the macro is working with in the current loop. The macro then uses the EntireRow.Insert method to insert a new blank row. If you want to insert two blank rows, do this twice.
  5. In Step 5, the macro loops back to increment the counter down.

How to Use This Macro

To use 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.
    Insert Module
  4. Type or paste the code in the newly created module.

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. Austin

    THIS IS AWESOME! Thank you for the clear explanation!

    • hashim khan
      hashim khan

      @Austin thanks so much sir for help me.

  2. Ali

    Very nice solution, better than any others I have tried. Thank you!

  3. Gerald

    You lost me with this VBA script.

    How about a script that simply prompts you for the start of the cell location and end cell location? Simple is always good.

  4. Ramakrishna

    can we have the possibility to get the pop up window asking how many rows wants to be inserted and from which cell, if yes please assist with Macro