Hello Excellers and time for some more #macromondays VBA fun. Today, I will show you how to write a Macro to insert extra rows into your excel worksheet. The number of rows will be variable, determined by how many rows the user types into an Input Box.
If you want more Excel and VBA tips then sign up for my monthly newsletter. I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
To see all of the blog posts in the Macro Mondays Series then click on the link below.
How To Excel At Excel – Macro Mondays Blog Posts.
What Does The Macro Do?
So, this Macro inserts the number of extra rows into the Excel worksheet. Rows are determined by the user who enters the number of rows to be inserted into an Input Box.
How Does It Work?
The Macro uses the Range technique where the range is selected and the subsequent number of rows are inserted.
Step 1. Open Visual Basic And Insert A New Module.
There are a couple of ways to do this. Hit ALT +F11 or Developer Tab – Visual Basic – Insert Module. Both have the same result. Now, you need to decide where you are going to insert that new module. There are two choices. In the current workbook you are working on, or your Personal Macro Workbook. So, what is the difference?.
To reuse the code over and over again, save the code in a module in your Personal Macro Workbook. If you want to read more about the Personal Macro Workbook then feel free to read 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?
Type Sub and the name of our macro (in the case it is InsertRows). Excel automatically enters the coding End Sub VB code. The rest of the code to insert rows is written between these two lines of code.
Sub InsertRows()
End Sub
Step 2. Declare Any Variables.
So, there is a variable to declare. Dim as Rng. This creates a memory container in Excel to store the number of rows the user wants to insert into the worksheet.
Dim Rows As Integer
Step 3. Using The INPUT Box.
The next bit of code uses the input box. This is to allow the user to specify how many rows they want to insert into the Excel worksheet. I have kept this very basic. Just one simple question. How Many Rows Do You Want To Insert?.
Rows = InputBox("How Many Rows Do You Want To Insert?")
Step 4. Identifying The Active Cell.
Excel determines the active cell by using the OFFSET function. By not specifying any rows or columns in the first part of this line of code the active cell is used. The number of rows to be inserted is then determined by the Rows variable supplied by the user. The code is offset by -1 to accommodate the current cell so the correct number of cells in the exact location are inserted.
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(Rows - 1, 0)).Select
Step 5. Insert The Correct Number Of Rows.
The next line of code inserts the number of rows specified by the user in the Input Box.
Selection.EntireRow.Insert
Step 6. The Code Ends After Excel Inserts The Rows.
Finally, once the correct number of rows have been inserted then the code ends. Excel already inserted this line of code automatically when the macro was named.
End Sub
So, of course the final part of any macro is to test it. Let’s see if it works. Yes it does. How useful is this?. The ability to insert a variable amount of rows the the user specifies
Do you want the full code to copy?.
Sub InsertRows()
'Macro by HowToExcelatexcel.com
Dim Rows As Integer
Rows = InputBox("How Many Rows Do You Want To Insert?")
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(Rows - 1, 0)).Select
Selection.EntireRow.Insert
End Sub
