Hello, Excellers and welcome back to another #MacroMonday #Excel blog post in my 2021 Excel series. Today I want to show you how to write s macro to insert and copy a number of new Excel rows into your spreadsheet. The number of rows, however, will be variable and will depend on a cell value.
This is a nice flexible alternative solution to inserting a standard number of rows after each data containing data in Excel. My YouTube video solution to that is linked below.
So, let’s get back to inserting rows into our Excel worksheet which are dependent on a cell value. If we take the example below. I have a list of customers and the number of visits they receive per week. I need a new line for each customer or each visit.
So, this will be a great bit of Excel VBA code to allow new rows. These will be inserted depending on how many visits are specified. You can see my sample data extract below.
So, for example, I need 5 rows in total for DER564, only 1 row for JJE612 and so on. So, let’s start working our way through this VBA code and create our macro.
Preparing To Write The Macro
First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
If you cannot see the Developer Tab you may need to enable it. Check out my very short YouTube video for instructions to do that.
You then have a choice. You can either create a module to store your code either in your Personal Macro Workbook or in your current workbook. What’s the difference?.
If you save the macro in your Personal Macro workbook it will be available for use in any of my Excel workbooks. If you store it in the current workbook then use is restricted to that workbook.
Learn More About Your Personal Macro Workbook (PMW)
If you want to read more about your Excel PMW then check out my blog posts below.
Creating and Updating Your Personal Macro Workbook
Create A Shortcut To Your Personal Excel Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Starting The Macro.
So, I have inserted a New Module into my current Workbook. Type Sub then the name of the Excel macro. In this example, I have named my Macro NewRows().
As you type the name of the Macro and hit return, Excel will automatically insert End Sub. Now, all that is needed is the rest of the code that will insert our blank rows in between these two lines of code.
[stextbox id=’info’]
Sub NewRows()
End Sub
[/stextbox]
Declaring Variables.
First, I need to declare some variables in this macro. This simply creates a memory container for the values. I have created the variable of Dim cell as Range and set the cell B2 as the first cell to contain a number.
[stextbox id=’info’]
Dim cell As Range
Set cell = Range(“B2”)
[/stextbox]
Using a Do-While Loop.
[stextbox id=’info’]
Do While Not IsEmpty(cell)
If cell > 1 Then
Range(cell.Offset(1, 0), cell.Offset(cell.Value – 1, _
0)).EntireRow.Insert
Range(cell, cell.Offset(cell.Value – 1, 1)).EntireRow.FillDown
End If
Set cell = cell.Offset(cell.Value, 0)
Loop
[/stextbox]
A do-while loop is almost exactly the same as a do until loop—there’s just one crucial difference. The loop continues until the statement at the beginning resolves to FALSE. So, in this example, the looping continues until an empty cell is encountered.
The loop inserts new multiple rows and then copies the row using the FillDown method. The cell variable is incremental to the next customer reference, and the loop continues until it reaches an empty cell.
Ending The Macro.
[stextbox id=’info’]
End Sub
[/stextbox]
Finally, once of all of the looping of rows have been completed then the code finally ends. This line of code already exists as was already entered into the module when started the type the name of the Macro.
Let’s Test The Macro!.
How cool is that?. Automatically inserting new rows into Excel based on a cell value.
Want To Copy The Code?.
[stextbox id=”grey”]
‘macro by howtoexcelatexcel.com
Sub NewRows()
Dim cell As Range
Set cell = Range(“B2”)
Do While Not IsEmpty(cell)
If cell > 1 Then
Range(cell.Offset(1, 0), cell.Offset(cell.Value – 1, _
0)).EntireRow.Insert
Range(cell, cell.Offset(cell.Value – 1, 1)).EntireRow.FillDown
End If
Set cell = cell.Offset(cell.Value, 0)
Loop
End Sub
[/stextbox]
So, if you want more Excel and VBA tips then sign up for my Monthly Newsletter. I share 3 Tips on the first Wednesday of the month. You will receive my free Ebook, 30 Excel Tips and check out all of my Macro Monday posts below.