Hello, Excellers welcome again to some more #MacroMonday Excel macro fun. I will who you how to insert a blank row easily into your Excel worksheet. Repeating this over and over again is very laborious. So, let’s take a look at automating with Excel VBA. This is an Excel query that one of my colleagues had to carry out manually every week. They needed to insert a blank row after every row in their range of cells.
The range of cells which need a blank row inserted can vary in number so we need a Macro which can self respond to the size of the range of cells selected by the user.
So, let’s get coding!
What Does The Macro Do?
This Macro will insert a blank row after every row in the user selection of rows.
Starting The VBA 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 the Developer Tab is not visible, then you may need to enable it. Feel free to watch my YouTube video on enabling the Developer Tab.
Before you begin to write any code you need to decide where to store the code. You have a choice.
- To store your code either in your Personal Macro Workbook or
- Store it in your current workbook
What Is The Difference In The Locations?.
So, there is a difference. If you save the code in your Personal Macro workbook it will be available in any Excel workbooks. Subsequently, if you store it in the current workbook then use is restricted to that workbook. In this instance, I may want to reuse the code so I will store it in my Personal Macro Workbook.
When you select this option then Excel creates (if it is not already created) this workbook and saves the macro in that location. By default, this macro workbook is named Personal.xlsb. This is a really useful macro that I want to reuse over and over again. So, I will make sure to save this in my Personal Macro Workbook.
If you want more details on creating and updating your personal macro workbook then I recommend my blog posts below.
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
Write The Code.
So, I have inserted a New Module into my Personal Macro Workbook. Type Sub then the name of the Excel macro. In this example, I have named my Macro InsertAlternateBlankRows.
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 in between these two lines of code.
[stextbox id=’info’]
Sub RemoveAllHyperLinks
End Sub
[/stextbox]
Declaring Variables.
Next, some variables need to be declared. This simply creates a memory container for the values. I have created the variables below which will store the range of cells selected by the user and the number of rows that will need to be inserted.
[stextbox id=’info’]
Dim rng As Range, CountRow As Integer, i As Integer
[/stextbox]
Setting The Range Of Cells.
For this part of the code, the range of cells is set by the user’s selection. In addition, the number of rows is stored to be used in the macro. This will be used to insert the blank rows.
[stextbox id=’info’]
Set rng = Selection
CountRow = rng.EntireRow.Count
[/stextbox]
Using The For Next VBA Looping Method.
Next, The For……Next looping method is used. This is used to loop through the first row in the range selection and insert a new entire row.
[stextbox id=’info’]
For i = 1 To CountRow
ActiveCell.EntireRow.Insert
[/stextbox]
Offsetting And Insert The New Rows.
New rows are offset by 2 rows and 0 columns in the range. This is repeated until the last rows of the selected range have been looped and the blank rows inserted.
[stextbox id=’info’]
ActiveCell.Offset(2, 0).Select
Next i
[/stextbox]
Ending The Macro.
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.
[stextbox id=’info’]
End Sub
[/stextbox]
Test The Insert Blank Row Macro!
The final part is to of course test your newly created Excel VBA macro. This really is my favourite part of the process. Now it is easy to insert a blank row into your Excel worksheet with VBA.
Want To Copy The Code?
[stextbox id=”grey”]
‘macro by howtoexcelatexcel.com
Sub InsertAlternateBlankRows()
Dim rng As Range, CountRow As Integer, i As Integer
Set rng = Selection
CountRow = rng.EntireRow.Count
For i = 1 To CountRow
ActiveCell.EntireRow.Insert
ActiveCell.Offset(2, 0).Select
Next i
End Sub[/stextbox]
So, if you want to see all of my blog posts in the #MacroMondays Series you can find them all in the link below. Why not bookmark it?, Yes, it is updated EVERY Monday.