VBA Express : Excel - Insert Blank Rows Between Existing Rows

Excel

Insert Blank Rows Between Existing Rows

Ease of Use

Easy

Version tested with

2003

Submitted by:

Anne Troy

Description:

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

Discussion:

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.

Code:

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:

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

Test the code:

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

Sample File:

insblankrows.zip 6.68KB

Approved by mdmackillop

This entry has been viewed 308 times.