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
Steps:
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.
Alternatively,
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.