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

Get FREE Advanced Excel Exercises with Solutions!

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).


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.

Data Set to Insert Rows Based On Cell Value with VBA in Excel

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.

Opening VBA Window to Insert Rows Based On Cell Value with VBA in Excel

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.

VBA Code to Insert Rows Based on Cell Value in Excel

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.

Saving the Workbook to Insert Rows Based On Cell Value with VBA in Excel

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.

Running Macro to Insert Rows Based On Cell Value with VBA in Excel

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).

Entering Input to Insert Rows Based On Cell Value with VBA in Excel

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).

Output to Insert Rows Based On Cell Value with VBA in Excel

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


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.

Code to Insert Rows Based On Cell Value with VBA in Excel

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 4: 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).

Entering Input to Insert Rows Based On Cell Value with VBA in Excel

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.

Entering Input to Insert Rows Based On Cell Value with VBA in Excel

Step 5: 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: Excel VBA: Insert Row with Values 


Download Practice Workbook


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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

8 Comments
  1. 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?

    • Hello, THOMAS SAULNIER!
      Yes! you can add 2 extra blank cells. For this, follow the code below. Hope this will help you!

      Sub Insert_Rows()
      For rng = Cells(Rows.Count, “C”).End(xlUp).Row To 2 Step -1
      For row_num = 2 To Cells(rng, “C”).Value + 3
      Cells(rng + 1, “C”).EntireRow.Insert
      Next row_num, rng
      End Sub

  2. This did not work for my table.

  3. Hello, E!
    Did you follow those steps properly? If any of those did not work, then try this out!

    Sub Inert_rows()
    Dim rng As Long
    For rng = range(“C” & Rows.Count).End(xlUp).Row To 1 Step -1
    With Cells(rng, 3)
    If IsNumeric(.Value) And Not IsEmpty(.Value) Then
    Rows(rng + 1).Resize(.Value).Insert
    range(Replace(“G#:BW#”, “#”, rng)).Copy Destination:=range(“G” & rng + 1).Resize(.Value)
    End If
    End With
    Next rng
    End Sub

  4. Hi!
    The macro from method #1 returns an error of “Runtime error -13. Type mismatch” on the line “For j = 1 To Int(Selection.Cells(i + Count, column_number))”

    Do you know how to solve?

    Thanks.

    • Hi Msirhc, thanks for reaching out. This actually happens because you haven’t typed the proper column in the first input box that shows up after running the code. The rows are inserted based on values, so if you select the first column (The week column), it won’t work, because that column contains text, not values. The second and third column of the dataset contain values. But the sales column contains irrelevant large values. So you should type 2 in the first input box if you want to use this dataset.

  5. Hi,

    I want to add new rows with certain information using a cell value. Is that possible?
    For eg. I have a row saying peanut butter and jelly sandwich, and i want the new rows to say 1. peanut butter, 2. jelly and 3. bread.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Sep 13, 2023 at 2:01 PM

      Hello AISHWARYA ROY

      Thanks for reaching out and posting your query with such clarity. You want to add new rows with certain information using a cell value. Some rows contain text that can be a mixture of three texts: peanut butter and jelly sandwich. You want to add the new rows accordingly and insert values like 1. Peanut butter, 2. Jelly and 3. Bread.

      I am delighted to inform you that I have developed an Excel VBA procedure that will fulfil your requirements.

      Navigate to Developer >> click on Visual Basic.

      Open the VBA editor

      Due to this, the VBA Editor window will appear.

      1. Hover over Insert and click on Module.

      2. Paste the following code and Run.

      
      Sub InsertRowsAndValues()
      
          Dim ws As Worksheet
          Dim i As Long
          Dim cell As Range
          Dim keywordCount As Integer
          
          Set ws = ThisWorkbook.Sheets("Sheet1")
          
          For i = 2 To 100
              Set cell = ws.Cells(i, "B")
              keywordCount = 0
              
              If InStr(1, cell.Value, "Peanut Butter", vbTextCompare) > 0 Then
                  keywordCount = keywordCount + 1
              End If
              
              If InStr(1, cell.Value, "Jelly Sandwich", vbTextCompare) > 0 Then
                  keywordCount = keywordCount + 1
              End If
              
              If InStr(1, cell.Value, "Bread", vbTextCompare) > 0 Then
                  keywordCount = keywordCount + 1
              End If
              
              If keywordCount > 0 Then
                  ws.Rows(i + 1 & ":" & i + keywordCount).Insert Shift:=xlDown
                  
                  For j = 1 To keywordCount
                      ws.Cells(i + j, "B").Value = j & ". " & Choose(j, "Peanut Butter", "Jelly Sandwich", "Bread")
                  Next j
                  
                  i = i + keywordCount
              End If
          Next i
      
      End Sub
      

      Insert the intended code and Run

      Finally, you will see an output like the following one.

      Output of running VBA code

      I am also attaching the solution workbook to help you understand better. Good luck!
      WORKBOOK

      Regards
      Lutfor Rahman Shimanto

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo