Method 1 – Using a VBA Macro to Insert Rows Based on the Cell Text Value in Excel
To insert a new row based on the specific text value “Insert Above” in the following dataset:
Steps:
- Press Alt + F11 or go to the tab Developer -> Visual Basic to open Visual Basic Editor.
- Click Insert -> Module.
- Enter the following code.
Sub InsertRowsBasedonCellTextValue()
'Declare Variables
Dim LastRow As Long, FirstRow As Long
Dim Row As Long
With ActiveSheet
'Define First and Last Rows
FirstRow = 1
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
'Loop Through Rows (Bottom to Top)
For Row = LastRow To FirstRow Step -1
If .Range("B" & Row).Value = "Insert Above" Then
.Range("B" & Row).EntireRow.Insert
End If
Next Row
End With
End Sub- Press F5 or click Run -> Run Sub/UserForm. You can also just click the small Play icon to run the macro.
A new row is inserted above “Insert Above”.
Read More: Insert Rows in Excel Based on Cell Value with VBA
Method 2 – Using a VBA Macro to Insert a Row Based on the Cell Numeric Value in Excel
Insert a new row above the cell containing 99:
Steps:
- Press Alt + F11 or go to the tab Developer -> Visual Basic to open Visual Basic Editor.
- Click Insert -> Module.
- Enter the following code.
Sub InsertBlankRowsBasedOnCellNumericValue()
Dim Col As Variant
Dim BlankRows As Long
Dim LastRow As Long
Dim R As Long
Dim StartRow As Long
Col = "C"
StartRow = 1
BlankRows = 1
LastRow = Cells(Rows.Count, Col).End(xlUp).Row
Application.ScreenUpdating = False
With ActiveSheet
For R = LastRow To StartRow + 1 Step -1
If .Cells(R, Col) = "99" Then
.Cells(R, Col).EntireRow.Insert Shift:=xlDown
End If
Next R
End With
Application.ScreenUpdating = True
End SubYour code is now ready to run.
- Run the code.
This is the output.
Read More: Excel VBA: Insert Row with Values
Method 3 – Using a Macro to Insert Multiple Rows Based on a Condition in Excel
To insert a row with specific values:
Steps:
- Press Alt + F11 or go to the tab Developer -> Visual Basic to open Visual Basic Editor.
- Click Insert -> Module.
- Enter the following code.
Sub InsertMultipleRows()
Qty = Range("E3").Value
Range("B12").Select
ActiveCell.Rows("1:" & Qty).EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Select
Range("B12").Resize(Qty, 2).Value = Range("C3:D3").Value
End Sub- Run the code.
- The selected values are inserted.
Read More: Excel Macro to Add Row to Bottom of a Table
Method 4 – Using a VBA Macro to Insert a Row after Each Record in Excel
Steps:
- Press Alt + F11 or go to the tab Developer -> Visual Basic to open Visual Basic Editor.
- Click Insert -> Module.
- Enter the following code.
Sub InsertRow()
Dim i As Long
For i = Cells(Cells.Rows.Count, "B").End(xlUp).Row To 3 Step -1
If Cells(i, "B") <> Cells(i - 1, "B") Then Rows(i).EntireRow.Insert
Next i
End Sub- Run the code.
This is the output.
Download Practice Template
Download the free practice Excel template.
Hello Sanjida. In the criteria3 method, of inserting rows based on a pre-defined value in a column, i want to insert the rows in a seperate sheet. how to do that ? can you please tell me
Greetings Prasanth,
Thanks a lot for the question you asked. Below I provide a code using which you can add rows in another worksheet. When you run the code, then you will see that the values stored in the C3:D3 insert in another worksheet. The values are now repeated according to the value mentioned in the cell E3