Insert Multiple Rows in Excel Based On Cell Value
In this blog post we will learn to Insert Multiple Rows in excel based on cell value with the help of VBA Code.
To avoid wasting our time by manually inserting rows one by one, we can use VBA code which will do our work within seconds.
Option Explicit
Sub Insert_Multiple_Rows_Based_On_Cell_Values()
Dim x As Integer
Dim i As Integer
Dim cell As Range
ActiveCell.Offset(1, 0).Select
For this we have prepared dummy data as shown in below image. In this data we have listed few “Products”, their “Quantities” and their “Unit Price”. Now we want to insert multiple rows based on their “Quantities”.
For example, “Product 1” has 04 quantities so we have to add 04 rows below the Product 1, “Product 2” has 02 quantities so we have to add 02 rows below the Product 2 and so on.
To do this, just follow the below steps:
1. Add “Developer Tab” on the Ribbon if it is not already added. If you are not aware about how to add Developer Tab on the Ribbon then click here. Once added on the Ribbon, “Developer Tab” will looks like as highlighted with red in below image.
2. Now, click on the “Visual Basic” command as highlighted in below image.
3. Once we click on “Visual Basic” command, it will open “Visual Basic Editor” as shown in below image.
4. Click on the “Insert” command from Visual Basic Editor.
5. Out of appeared options, click on “Module”.
6. This will insert “Module 1” in our Visual Basic Editor.
7. We have to enter the “VBA Code” in red highlighted area of Module 1.
8. Write the VBA Code as given below in Module 1.
Sub Insert_Multiple_Rows_Based_On_Cell_Values()
Dim x As Integer
Dim i As Integer
Dim cell As Range
Set cell = Range("C2")
cell.Select
cell.Select
Do While ActiveCell.Value > 0
i = ActiveCell.Value
i = ActiveCell.Value
If ActiveCell.Value > 0 Then
For x = 1 To i
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
Next x
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
Next x
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub
12. Once we click on “Save” button, macro enabled file will get saved on the selected path.
13. Now, click on “Macros” command under Developer Tab.
15. Once we click on “Run” button, we can see that multiple rows based on cell values will get inserted in excel sheet.
End Sub
9. Now, click on “Save” button as highlighted in below image or else we can also press “Ctrl + S” on our keyboard to save the VBA Code written in Module 1.
10. Once we click on “Save” button, one dialog box will appear on our screen. We have to click on “No” button.
11. Once we click on “NO” button then “Save As” dialog box will appear on excel screen. Under “Save as type” select “Excel Macro-Enabled Workbook (*xlsm)” as highlighted in red in below image.
14. This will open Macro dialog box where we can see list of all the macros. Currently there is only one macro available, hence we can see only one name. Select this macro and then click on “Run” button.
You can also try this code at your end. In case of any queries write us in the comments section.
hello, I have some rows with value 0, so this code doest work, any idea on how to do it, like if it says 0, it should not insert for that particular ro.
ReplyDeleteHello, Thank you for raising above concern. The VBA code mentioned in above blog post is "only" for non zero values.
DeleteIf your data contains 0 then please use below VBA code:
Option Explicit
Sub Insert_Multiple_Rows_Based_On_Cell_Values()
Dim x As Integer
Dim i As Integer
Dim cell As Range
Set cell = Range("C2")
cell.Select
Do While ActiveCell.Value > 0
i = ActiveCell.Value
If ActiveCell.Value > 0 Then
For x = 1 To i
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert
Next x
ActiveCell.Offset(1, 0).Select
End If
If ActiveCell.Value = 0 Then
ActiveCell.Offset(1, 0).Select
End If
Loop
ActiveCell.Offset(-1, 0).Select
End Sub
Hello,
DeleteThis worked for me for only some of my rows in my data (up to line 14) . I want this to do it for my whole data set which has over 2000 rows. How do I do this?
Hello, Thank you for your feedback. Can you please provide screenshot of your raw data on which you are using above macro? This will help us to provide exact solution.
DeleteI want to insert only 12 rows, including that row in which the value 12 is mentioned. What happens is that when I run the above code, it gives me 12 empty rows, including the first row in which information is given. Now the total number of rows I have is 13, but I only need 12.
ReplyDeleteOne more thing I want with the code is that along with the empty row, I also need the date to be increased along with the rows. Suppose in the first row date is 12-March-2020 and row I want is 12, so from the next empty rows date should be 12-April-2020, in the third row date should be 12-May-2020 like this in every row.
Hello,
DeleteThe above macro is written keeping in mind that we want to add 12 rows below the row in which input 12 is written. To achieve your condition along with the requirements you mentioned about date, we will need to make some adjustments in above code.
We request you to please send your all the detailed requirements on our email ID: exceltrickswithtejraj@gmail.com so that we can assist you better.
I want to insert only 12 rows, including that row in which the value 12 is mentioned. What happens is that when I run the above code, it gives me 12 empty rows, including the first row in which information is given. Now the total number of rows I have is 13, but I only need 12.
ReplyDeleteOne more thing I want with the code is that along with the empty row, I also need the date to be increased along with the rows. Suppose in the first row date is 12-March-2020 and row I want is 12, so from the next empty rows date should be 12-April-2020, in the third row date should be 12-May-2020 like this in every row.
Hello,
DeleteThe above macro is written keeping in mind that we want to add 12 rows below the row in which input 12 is written. To achieve your condition along with the requirements you mentioned about date, we will need to make some adjustments in above code.
We request you to please send your all the detailed requirements on our email ID: exceltrickswithtejraj@gmail.com so that we can assist you better.
This almost works for me. The only difference is that I only want to add the correct number of rows according to my cell value if that cell value is greater than 1. If the cell value if either 0 or 1 I need the macro to skip those rows and then carry on. How do I achieve this?
ReplyDeleteHello,
DeleteThe above macro is written keeping in mind certain conditions such as that we will add 3 rows below the row in which input 3 is written. To achieve your condition , we will need to make some adjustments in above code.
We request you to please send your all the detailed requirements on our email ID: exceltrickswithtejraj@gmail.com so that we can assist you better.
I am wanting something similar to this. I want to be able to type yes into a cell (or have a yes/no drop down box) and then say 5 extra rows to appear depending on whether the cell above is a yes or no.
ReplyDeleteHello,
DeleteThe above macro is written keeping in mind certain conditions. To achieve your desired output with additional condition of Yes/No, we will need to make some adjustments in above code.
We request you to please send your all the detailed requirements along with screenshot of your desired output on our email ID: exceltrickswithtejraj@gmail.com so that we can assist you better.