In this article, Iâ€™ll show you** how you can insert rows in Excel** based on cell value with **VBA.** Youâ€™ll learn to insert rows after each row, as well as after a fixed interval of rows.

Insert Rows Based On Cell Value with VBA in Excel (Quick View)

`Cells(2,3).EntireRow.Insert`

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

**Download Practice Workbook**

**2 Methods to Insert Rows Based On Cell Value with VBA in Excel**

Here weâ€™ve got a data set with the Sales Record of Last 5 Months of a company.

We have the **Week No, Number of Products** sold, and the **Total Sales** of that week.

Now we need to insert data below each week about each of the products sold in that week.

Thatâ€™s why we need to 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.

Below **Week 2**, there need to be **4** empty rows.

And so on.

Our objective today is to insert empty rows within this data set based on the cell value of the column **Number of Products**.

Weâ€™ll use **VBA codes** to accomplish this.

**1. Insert Rows Based On Cell Value Above/Below Each Row with VBA in Excel**

First of all, 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:**

**â§Ş**** Step 1: Opening the VBA Window**

**âž¤** Press **ALT+F11** on your keyboard. The **VBA **window will open.

**â§Ş**** Step 2: Inserting a New Module**

**âž¤** Go to the **Insert** tab in the** VBA** window.**Â **

**âž¤** From the options available, select **Module**.

**â§Ş**** Step 3: Entering the VBA Code**

**âž¤** A new module window called **â€śModule 1â€ť **will open.**Â **

**âž¤** 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**.

**â§Ş**** Step 4: Saving the Macro-Enabled Workbook**

**âž¤** Save the workbook as **Excel Macro-Enabled Workbook**.

**â§Ş**** Step 5: Selecting the Data Set**

âž¤ Return to your worksheet.

âž¤select the range of cells within which you want to enter the rows. Here Iâ€™ve selected

**B4:D8** (Without **Column Headers**).

**â§Ş**** Step 6: Running the Macro**

âž¤ Then 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**.

**â§Ş**** Step 7: Entering the InputsÂ **

**âž¤****Â **You will get two Input Boxes. The **1st** 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 **2nd** box will ask you to enter **0** to insert rows above each row. And **1** to insert rows below each row.

**âž¤** I want to enter rows below each row. So Iâ€™ve entered **1**.

**â§Ş**** Step 8: The Final Output!**

**âž¤** Then click **OK**.

**âž¤** you will find empty rows inserted below each of your rows according to your need (Marked in light brown for visualizing).

**Read More:** **VBA to Insert Row in Excel (11 Methods)**

**Similar Readings**

**How to Insert a New Row in Excel (Top 5 Methods)****Shortcuts to Insert New Row in Excel (6 Quick Methods)****Excel Fix: Insert Row Option Greyed out (9 Solutions)****Excel Formula to Insert Rows between Data (2 Simple Examples)****How to Insert Multiple Blank Rows in Excel (4 Easy Ways)**

**2. Insert Rows Based On Cell Value After a Fixed Interval with VBA in Excel**

Now we donâ€™t want to enter empty rows after each row.

Rather we want to insert empty rows after a fixed interval of rows.

For example, letâ€™s think that we want to 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:**

**â§Ş**** Step 1: The Initial Steps**

**âž¤** Run Steps **1-4** of method 1 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.

Just in line **7**, we have added the term **step interval**.

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

**â§Ş**** Step 2: Selecting the Data Set**

âž¤ Then come back to your worksheet.

âž¤ select the range of cells within which you want to enter the rows. Here Iâ€™ve selected

**B4:D8** (Without **Column Headers**).

**â§Ş**** Step 3: Running the Macro**

âž¤ Then 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**.

**â§Ş**** Step 7: Entering the InputsÂ **

**âž¤****Â **This time**, **you will get three Input Boxes. The **1st** 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 **2nd** box will ask you to enter **0** to insert rows above each row. And **1** to insert rows below each row.

**âž¤** I want to enter rows below each row. So Iâ€™ve entered **1**.

**âž¤** And the **3rd** box will ask you to enter the fixed interval. As I want to insert rows after every **2** rows, I have entered it as **2**.

**â§Ş**** Step 8: The Final Output!**

**âž¤** Then click **OK**.

**âž¤** you will find empty rows inserted after every 2 rows depending on the cell value (Marked in light brown for visualizing).

**Read More:** **VBA Macro to Insert Row in Excel Based on Criteria (4 Methods)**

**Conclusion**

Using these methods, you can insert rows based on cell values within any dataset with **VBA** in Excel. Do you know any other method? Or do you have any problems? Feel free to ask us.

for the method described in Insert Rows Based On Cell Value Above/Below Each Row with VBA in Excel is it possible to amend the code to Add 2 extra blanks based on the value in the cell? so in your example Week 1 would have 5 blank lines insteady of 3?