Insert Blank Rows Between Existing Rows

Ease of Use


Version tested with


Submitted by:

Anne Troy


Inserts a blank row between each existing row in a contiguous range of data at the insertion point. 


While we don't recommend keeping a spreadsheet in this format because it can easily create problems with formulas, there may be times when adding a blank row between each existing row is necessary. Use this macro. 


instructions for use


Sub Insert_Blank_Rows() 'Select last row in worksheet. Selection.End(xlDown).Select Do Until ActiveCell.Row = 1 'Insert blank row. ActiveCell.EntireRow.Insert shift:=xlDown 'Move up one row. ActiveCell.Offset(-1, 0).Select Loop End Sub

How to use:

  1. Save a copy of your original file for safekeeping.
  2. Open the workbook and hit Alt+F11 to view the Visual Basic Editor (VBE).
  3. Choose your workbook at left and hit Insert-Module.
  4. Paste the code (from above) into the code window that appears at right.
  5. Hit the Save diskette.
  6. Close the VBE.

Test the code:

  1. Place your cursor in any cell that contains data.
  2. Hit Tools-Macro-Macros and double-click Insert_Blank_Rows.

Sample File:

insblankrows.zip 6.68KB 

Approved by mdmackillop

This entry has been viewed 302 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express