Hello, Excellers. Welcome back to another #MacroMonday #Excel blog post in my Excel Macro 2020 Series. Today I will show you how to insert Multiple rows into Excel. The user will specify how many rows to insert via an input box.
This is a really flexible alternative solution to inserting a standard number of rows after each data containing data in Excel. So, it puts the user in full control of where and how many black rows to insert.
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.
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)
Creating and Updating Your Personal Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Create A Shortcut To Your Personal Excel Macro Workbook
Starting The Macro
I will start macro by inserting a New Module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro. In this example, I have called it simply InsertNewRows. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We simply need to enter the rest of the code between these two lines
[stextbox id=’info’]
Sub InsertNewRows()
End Sub
[/stextbox]
Declaring Variables.
Next, I will declare two variables in this macro. This simply creates memory containers in Excel for these values. The two values of the number of new rows and the counter used in the code are declared as integers.
[stextbox id=’info’]
Dim newRows As Integer
Dim counter As Integer
[/stextbox]
Selecting The Current Row.
The entire row of the cell that the user has selected is selected in its entirety.
[stextbox id=’info’]
ActiveCell.EntireRow.Select
[/stextbox]
Using An Input Box To Decide Rows To Insert.
The next piece of code will display an input box to request the number of rows to insert into the worksheet. This will be stored as the first variable we declared in out Dim statements. The Input Box in Excel VBA has the following syntax
expression.InputBox (Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)
In this macro, the expression is newRows, which represents the number of rows to insert. I have only used the first two parameters of Prompt, (Enter number of rows to insert) and Title (Insert Rows).
[stextbox id=’info’]
newRows = InputBox(“Enter number of rows to insert”, “Insert Rows”)
[/stextbox]
Insert Rows Using The For Next Loop.
This next bit of code uses the For Next Loop statement. This code takes the value entered by the user and counts from 1 to this value. New rows are inserted until the correct number of rows are inserted.
[stextbox id=’info’]
For counter = 1 To newRows
Selection.Insert Shift:=xlToDown
Next counter
[/stextbox]
Ending The Macro.
Finally, the code ends once all looping of cells has been completed with the “End Sub” piece of code. This was already entered into the module when the name of the macro was set.
What Next? Want More Tips?
So, if you want more tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Macro Monday series. Click on the link below
Do You Need Help With An Excel Problem?.
Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST. All you need to do is choose the Excel task that most closely describes what you need to be done. Above all, there is a money back guarantee and similarly Security and Non-Disclosure Agreements. Try It!. Need Help With An Excel VBA Macro?. Of course, you don’t need to ask how to list all files in a directory as it is right here for free.
