Imagine you’re working on a sales report in Excel, and you need to add rows between each salesperson’s records to make the report easier to read. You have a list of sales data where each salesperson has multiple rows of data, and you want to insert a blank row after every three rows to separate each salesperson’s section.
This tutorial will discuss different ways to write code in VBA to create a macro that can help you insert rows in a worksheet.
Insert a Single Row using VBA
To insert a row using a VBA code, use the “Entire Row” property with the “Insert” method. With the entire row property, you can refer to the entire row using a cell and then insert a new row there. By default, it will insert a single row before the cell that you have mentioned.
- First, specify a cell using the range object.
- Now, enter a dot (.) to get the list of properties and methods.
- After that, select the “Entire Row” property or type it.
- Again, enter a dot (.), select the “Insert” method, or type it.
Range("A1").EntireRow.Insert Your code is ready here to insert a row. When you run this code, it will instantly insert a new row before cell A1. If you want to insert a column, you can use the code below:
Range("A1").EntireColumn.Insert Insert Multiple Rows
I have found two ways to insert multiple rows in a worksheet. The first is the same insert method we used in the above example.
With this, you need to specify a range whose count is equivalent to the number of rows you want to insert. Now, let’s say you want to insert 5 rows after; in that case, you can use a code like the following.
To be honest, I haven’t found this method quite useful because you need to change the range to change the row count. So here’s the second method.
Dim iRow As Long
Dim iCount As Long
Dim i As Long
iCount = InputBox(Prompt:="How many rows you want to add?")
iRow = InputBox _
(Prompt:="After which row you want to add new rows? (Enter the row number")
For i = 1 To iCount
Rows(iRow).EntireRow.Insert
Next i
When you run this code, it asks you to enter the number of rows you want to add and the row number where you want to add all those rows. It uses a FOR LOOP (For Next) to loop that number of times and insert rows one by one.
Note: If a worksheet is protected, you won’t be able to insert rows using VBA code. But you can still insert rows if the worksheet protection allows it. This can be done by enabling the “Insert Rows” option when you protect the worksheet.
Insert Rows Based on the Cell Values
If you want to insert rows based on a cell value, use the following code.
Dim iRow As Long
Dim iCount As Long
Dim i As Long
iCount = Range("A1").Value
iRow = Range("B1").Value
For i = 1 To iCount
Rows(iRow).EntireRow.Insert
Next i
When you run this macro, it takes the count of rows from cell A1 and the row where you want to add rows from cell B1.
Insert a Row without Formatting
When you insert a row where the above row has some specific formatting, the row will also have that formatting automatically. And the simplest way to deal with this is to use clear formats. Consider the following code.
Rows(7).EntireRow.Insert
Rows(7).ClearFormats
When you run the above code, it inserts a new row before the 7th row. That new row becomes the 7th row, and the second line of code clears the formats from that row.
Insert Copied Row
You can also use the same method to copy a row and then insert it somewhere else. See the following code.
Application.CutCopyMode = False
With Worksheets("Data")
.Rows(5).Copy
.Rows(9).Insert Shift:=xlShiftDown
End With
Application.CutCopyMode = True
Insert Rows Based on the Selection
It inserts rows into a worksheet based on the number of rows you have selected. First, it sets the active worksheet and identifies the range of cells you’ve selected.
Then, it counts the number of rows in your selection and inserts that number of rows starting at the first row of your selection, pushing existing rows down to make space.
Sub insert_rows_based_on_selection()
Dim ws As Worksheet
Dim selectedRange As Range
Dim numRows As Integer
Set ws = ActiveSheet
Set selectedRange = Selection
numRows = selectedRange.Rows.Count
' Insert rows based on the number of rows selected
ws.Rows(selectedRange.Row & ":" & selectedRange.Row + numRows - 1).Insert Shift:=xlDown
MsgBox numRows & " rows inserted at position " & selectedRange.Row & "."
End Sub
After inserting the rows, a message tells you how many rows were inserted and at which position. This allows you to quickly add multiple rows at a specific location in your worksheet by simply selecting the desired range and running the code.
Insert Alternate Rows from a Specific Cell
This code helps you insert alternate rows starting from a cell you select. First, it asks you to choose the starting cell; then, it asks how many alternate rows you want to insert.
For example, if you select cell A1 and want to insert three rows, they will be inserted after every other row starting from A1.
Sub insert_alternate_rows()
Dim ws As Worksheet
Dim rng As Range
Dim startCell As Range
Dim numRows As Integer
Dim i As Integer
' Set the worksheet to the active sheet
Set ws = ActiveSheet
' Prompt the user to select the starting cell
On Error Resume Next
Set startCell = Application.InputBox("Select the starting cell", Type:=8)
On Error GoTo 0
' Check if the user selected a cell
If startCell Is Nothing Then
MsgBox "No cell selected. Exiting..."
Exit Sub
End If
' Prompt the user to input the number of alternate rows to insert
numRows = Application.InputBox("Enter the number of alternate rows to insert:", Type:=1)
' Check if the user entered a valid number
If numRows <= 0 Then
MsgBox "Invalid number. Exiting..."
Exit Sub
End If
' Insert the alternate rows
For i = 1 To numRows
startCell.Offset((i - 1) * 2).EntireRow.Insert Shift:=xlDown
Next i
MsgBox "Done! " & numRows & " alternate rows inserted."
End Sub
The code works by looping through the number of rows you entered and inserting a row at every second position from the starting cell.
After the loop, a message shows how many rows were inserted. This allows you to quickly add rows to your worksheet without manually doing it.
Insert Rows at Every Nth Row
It inserts rows at every nth interval in a worksheet. It starts by setting the active worksheet and defining the interval (n) as every 3rd row. It also specifies the starting row (startRow) as 2 and the ending row (endRow) as 20.
The code then loops from the end to the start row, inserting a row at every nth position and working backward to ensure the correct rows are shifted down.
Sub insert_rows_every_nth_row()
Dim ws As Worksheet
Dim i As Long
Dim n As Long
Dim startRow As Long
Dim endRow As Long
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your specific sheet name
n = 3 ' Specify the interval (every nth row)
startRow = 2 ' Specify the starting row
endRow = 20 ' Specify the ending row
For i = endRow To startRow Step -n
ws.Rows(i).Insert Shift:=xlDown
Next i
MsgBox "Rows inserted at every " & n & "th row between rows " & startRow & " and " & endRow & "."
End Sub
After inserting the rows, a message appears stating that rows were inserted at every third row between the specified start and end rows.
Insert Row with Formatting
When you run this code, it inserts the selected row multiple times into a specified cell location in the worksheet. First, it checks if you have chosen a single row. If not, it asks you to select one.
Then, it asks you to choose a cell where the new rows will be inserted and how many times you want to insert the selected row.
After you specify the cell and the number of times, it copies the selected row and inserts it the specified number of times at the specified location, maintaining the original row’s formatting.
Finally, it shows a message box confirming the number of rows inserted.
Sub insert_row_with_formatting()
Dim ws As Worksheet
Dim selectedRow As Range
Dim insertCell As Range
Dim numRows As Integer
Dim i As Integer
' Set the worksheet to the active sheet
Set ws = ActiveSheet
' Check if a single row is selected
If Selection.Rows.Count <> 1 Then
MsgBox "Please select a single row to copy and insert."
Exit Sub
End If
Set selectedRow = Selection.Rows(1)
' Prompt the user to select the cell where rows will be inserted
On Error Resume Next
Set insertCell = Application.InputBox("Select the cell where rows will be inserted:", Type:=8)
On Error GoTo 0
' Check if the user selected a cell
If insertCell Is Nothing Then
MsgBox "No cell selected. Exiting..."
Exit Sub
End If
' Prompt the user to input the number of times to insert the row
numRows = Application.InputBox("Enter the number of times to insert the row:", Type:=1)
' Check if the user entered a valid number
If numRows <= 0 Then
MsgBox "Invalid number. Exiting..."
Exit Sub
End If
' Insert the row with formatting the specified number of times
For i = 1 To numRows
selectedRow.Copy
insertCell.EntireRow.Insert Shift:=xlDown
insertCell.PasteSpecial Paste:=xlPasteFormats
Next i
' Clear the clipboard
Application.CutCopyMode = False
MsgBox numRows & " rows inserted at the specified cell."
End Sub
Insert Rows on Multiple Sheets
This code helps you insert rows across all worksheets in the workbook. First, it asks you to enter the row number where you want to insert new rows and checks if the number is valid. Then, it asks how many rows you want to insert and ensures this number is valid.
After that, it loops through each worksheet in the workbook and inserts the specified number of rows at the specified row number on each sheet. Finally, it shows a message confirming how many rows were inserted and at which position.
Sub insert_rows_on_multiple_sheets()
Dim ws As Worksheet
Dim rowNum As Long
Dim numRows As Long
Dim i As Long
' Prompt the user to input the row number where rows will be inserted
rowNum = Application.InputBox("Enter the row number where rows will be inserted:", Type:=1)
' Check if the user entered a valid row number
If rowNum <= 0 Then
MsgBox "Invalid row number. Exiting..."
Exit Sub
End If
' Prompt the user to input the number of rows to insert
numRows = Application.InputBox("Enter the number of rows to insert:", Type:=1)
' Check if the user entered a valid number
If numRows <= 0 Then
MsgBox "Invalid number of rows. Exiting..."
Exit Sub
End If
' Loop through each worksheet in the workbook
For Each ws In ThisWorkbook.Worksheets
' Insert the specified number of rows at the specified row number
For i = 1 To numRows
ws.Rows(rowNum).Insert Shift:=xlDown
Next i
Next ws
MsgBox numRows & " rows inserted at row " & rowNum & " on each sheet."
End Sub
If you try adding new rows where cells are merged, you need to check if your codes work correctly. As I have tested, adding rows within the merged cells makes cells stay merged, while adding new rows extends those merged cells. Let’s say three cells are merged, and you enter two more rows within the merged cells. Now, you will have five merged cells after adding new rows. But I’d still suggest you test your code before dealing with merged cells.