How to insert a blank row after every row in Excel – Tips + VBA code

Microsoft Excel has rows and columns that are divided into cells. However, you may sometimes need to add blank rows between your already existing dataset. There's no direct way of adding rows in-between datasets in Excel. Thankfully, Excel has workarounds that can be used to add blank rows. This post will discuss simple ways and tips for inserting blank rows in an Excel dataset.

Using VBA to insert Blank Rows

VBA helps automate the process of inserting blank rows and columns in Excel. Let us learn some of the steps to follow when using this method:

1. Open the Excel application.

2. Select the dataset in which you want to add blank rows.

3. Click on the Developer tab on the Ribbon, and then locate the Visual Basic button. Alternatively, press the Alt + F11 keys on your keyboard to open the Visual Basic feature.

4. In the Visual Basic screen, click the Insert tab on the Ribbon and select the Module button.

5. Type the following code in the empty module.

Sub InsertAlternateRows()

'This code will insert a row after every row in the selection

Dim rng As Range

Dim CountRow As Integer

Dim i As Integer

Set rng = Selection

CountRow = rng.EntireRow.Count

For i = 1 To CountRow

ActiveCell.Offset(1, 0).EntireRow.Insert

ActiveCell.Offset(2, 0).Select

Next i

End Sub

How to run the above VBA code


1. While on the Visual Basic screen, press the F5 key to run the code.

2. Alternatively, click on the Run tab on the toolbar. From the menu, click the Run sub/UserForm button to run the code.


1. Close the Visual Basic screen.

2. Click on the Developer tab on the Ribbon, and then locate the Macros button.

3. In the Macros dialogue box, select the macro name of the macro that is responsible for inserting blank rows.

4. Click on the Run button to run the VBA code.

A manual method of inserting blank rows

If you find the VBA method hard to follow, there is still another more straightforward manual method of inserting blank rows. However, this method is tiresome and time-consuming in case of a large dataset. If you still need to use this method, here are the steps that you need to follow:

1. Open the Excel application.

2. Open the dataset that you need to add blank rows.

3. Highlight one row that will border the inserted row.

4. Right-click on the row you need to add another blank row.

5. Click on the Insert button. An empty row will be inserted region.

6. Do the above steps to the rest of the worksheet.