Insert Rows in Excel Based on Cell Value with VBA (2 Methods)

This line inserts a row above cell C2 (Row 2, Column 3).

Insert Rows Based on Cell Value with VBA: 2 Methods

Here we’ve got a data set with the Sales Record of the Last 5 Months of a company. We have the Week No, Number of Products sold, and the Total Sales of that week.

We need to insert data below each week about each of the products sold in that week, so we’ll insert empty rows above or below each week equal to the number of products sold in that week.

For example, below Week 1, there need to be 3 empty rows. We’ll use VBA code to accomplish this.

Method 1 – Insert Rows Based on Cell Value Above/Below Each Row with VBA in Excel

We’ll insert empty rows above or below each week based on the cell value of the column Number of Products. You can use the following VBA code to execute this:

⧭ Code:

Sub Insert_Rows()

Column_Number = Int(InputBox("Number of the Column on Which the Cell Values Depend: "))

Above_or_Below = Int(InputBox("Enter 0 to Insert Rows Above Each Row." + vbNewLine + "OR" + vbNewLine + "Enter 1 to Insert Rows Below Each Row."))

Dim Count As Integer
Count = 0

For i = 1 To Selection.Rows.Count
    For j = 1 To Int(Selection.Cells(i + Count, Column_Number))
        Selection.Cells(i + Above_or_Below + Count, Column_Number).EntireRow.Insert
        Count = Count + 1
    Next j
Next i

End Sub

⧪ Note: This code produces a Macro called Insert_Rows.

⧭ Step by Step Procedure to Run the Code:

• Press Alt + F11 on your keyboard. The VBA window will open.

• Go to the Insert tab in the VBA window. 
• Select Module.

• Insert the given VBA code in the opened Module.

⧪ Explanation of the Code:

• Sub Insert_Rows() starts a new Macro called Insert_Rows.
• Column_Number = Int(InputBox(“Number of the Column on Which the Cell Values Depend: “)) asks you to enter the number of the column of your selected range on which the cell values depend.
• Above_or_Below = Int(InputBox(“Enter 0 to Insert Rows Above Each Row.” + vbNewLine + “OR” + vbNewLine + “Enter 1 to Insert Rows Below Each Row.”)) asks you to enter a 0 to insert empty rows above each row, and 1 for below each row.
• Dim Count As Integer declares a new integer called Count.
• Count=0 sets the initial value of Count to 1.
• For i = 1 To Selection.Rows.Count starts an iteration number of times equal to the number of rows of your selected range.
• For j = 1 To Int(Selection.Cells(i + Count, Column_Number)) starts an iteration number of times equal to the value of the specific cell.
• Selection.Cells(i + Above_or_Below + Count, Column_Number).EntireRow.Insert inserts the required number of empty rows above or below each row.
• Count=Count+1 increases the value of Count as 1.
• Next j ends the 2nd for-loop.
• Next i ends the 1st for-loop.
• End Sub ends the Macro.

• Save the workbook as Excel Macro-Enabled Workbook.

• Return to your worksheet.
• Select the range of cells within which you want to enter the rows. We’ve selected B4:D8 (Without Column Headers).

• Press ALT+F8 on your keyboard.
• A dialogue box called Macro will open. Select Insert_Rows (The name of the Macro) and click on Run.

• You will get two Input Boxes. The first box will ask you to enter the number of the column on which the cell value depends. For this example, it is 2 (Number of Products).

• The second box will ask you to enter 0 to insert rows above each row or 1 to insert rows below each row. We want to enter rows below each row, so we have entered 1.

• Click OK.
• You will find empty rows inserted below each of your rows (Marked in light brown).

Read More: How to Use VBA to Insert Row in Excel

Method 2 – Insert Rows Based on Cell Value After a Fixed Interval with VBA in Excel

We’ll enter empty rows after every 2 rows, depending on the value of the cell.

You can accomplish this using this VBA code:

⧭ Code:

Sub Insert_Rows_After_a_Fixed_Interval()

Column_Number = Int(InputBox("Number of the Column on Which the Cell Values Depend: "))

Above_or_Below = Int(InputBox("Enter 0 to Insert Rows Above Each Row." + vbNewLine + "OR" + vbNewLine + "Enter 1 to Insert Rows Below Each Row."))

Interval = Int(InputBox("Enter the Fixed Interval"))

Dim Count As Integer
Count = 0

For i = 1 To Selection.Rows.Count Step Interval
    For j = 1 To Int(Selection.Cells(i + Count, Column_Number))
        Selection.Cells(i + Above_or_Below + Count, Column_Number).EntireRow.Insert
        Count = Count + 1
    Next j
Next i

End Sub

⧪ Note: This code produces a Macro called Insert_Rows_After_a_Fixed_Interval.

⧭ Step by Step Procedure to Run the Code:

• Open the VBA window, insert a new module, insert this VBA code, and then save the workbook.

⧪ Explanation of the Code:

The explanation of the code is the same as the earlier code.

In line 7, we have added the term step interval.

It forces the loop not to iterate through each row of the selected range but to iterate after a fixed number of rows called Interval.

⧪ Using the Code:

• Come back to your worksheet.
• Select the range of cells within which you want to enter the rows. We’ve selected B4:D8 (Without Column Headers).

• Press ALT+F8 on your keyboard.
• A dialogue box called Macro will open. Select Insert_Rows_After_A_Fixed_Interval (The name of the Macro) and click on Run.

• You will get three Input Boxes. The first box will ask you to enter the number of the column on which the cell value depends. For this example, it is 2 (Number of Products).

• The second box will ask you to enter 0 to insert rows above each row and 1 to insert rows below each row. We want to enter rows below each row, so we’ve entered 1.

• The third box will ask you to enter the fixed interval. We want to insert rows after every 2 rows, so have entered 2.

• Click OK.
• You will find empty rows inserted after every 2 rows depending on the cell value (Marked in light brown).

Read More: Excel VBA: Insert Row with Values 

Download the Practice Workbook

Related Articles

• VBA Macro to Insert Row in Excel Based on Criteria
• Excel Macro to Add Row to the Bottom of a Table
• Macro to Insert Row and Copy Formula in Excel