Need to now how to insert blank rows in Excel?.  Hello Excellers and welcome back to another #MacroMonday #Excel blog post in my 2020 series. Today’s blog post is an alternative macro or VBA solution to a very popular YouTube video I have already published.  It is a very common question.  Just how to do I insert blank rows between existing rows in Excel?.

The link to a non VBA or Macro solution to this problem can be seen below.

But, back to those of us who want a VBA or Macro solution, let’s get coding.

Preparing To Write The Code.

First of all, I want to make sure I have an Input Folder and an Output folder. I have created these on my Desktop. The Input folder contains my CSV files.

Next, 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 actually 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 you code in your Personal Macro workbook it will be available for use in any of my Excel workbooks.  If the macro is stored in the current workbook then use is restricted to that workbook.  This code will be useful to reuse in any workbook. I will create and save this for future use in my Personal Macro 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.

Macro Mondays -Creating and Updating Your Personal Macro Workbook Macro Mondays

 Create A Shortcut To Your Personal Excel Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

Starting The Macro.

We need to start the 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 Insert Blank Rows.  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

Declaring Variables

First, I will declare a variable in this macro. This simply creates a memory container in Excel for this value. This is the range of cells that will be used to determine where Excel inserts the blank rows. I have also then set the range of cells as to be those selected by the user.

[stextbox id=’info’]
Dim Rng As Range
Set Rng = Selection
[/stextbox]

Turning Off Screen Updates.

The next line of code turns off any screen updates. If you have ever seen the screen flicker when you run a macro, then this small bit of code prevent it. Just remember to turn the screen updates back on at the end of the code. We will cover that later down below.

[stextbox id=’info’]
Application.ScreenUpdating = False
[/stextbox]

Selecting The Range Of Cells.

This simple line of code sets the user-selected range of cells ready for the next line of code.

[stextbox id=’info’]
Rng.Select
[/stextbox]

Use The Do While Loop To Insert Blank Rows.

I am using the Do While loop in this macro. It is useful when you want to repeat a set of statements while a condition is true. The condition can be checked at the beginning or at the end of the loop. The condition is that the active cell is empty. Until that active cell is empty, then take the active cell and insert one row. The active cell is offset by 2 rows. This will prevent early termination of looping due to a row being inserted which is blank as the condition is to insert rows until the active cell is empty.

[stextbox id=’info’]
Do Until IsEmpty(ActiveCell) = True
Rows(ActiveCell.Row + 1).Insert
ActiveCell.Offset(2, 0).Select
Loop
[/stextbox]

Turning On Screen Updates.

Next, I will reverse screen updating. This code will turn on-screen updates again.

[stextbox id=’info’]
Application.ScreenUpdating = False
[/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 for us when started the type the name of the macro.

[stextbox id=’info’]
End Sub
[/stextbox]

Finally, Test The Macro! Insert Blank Rows.

Do You Want To Copy The Code?.

[stextbox id=’info’]
‘Macro By HowtoExcelExcel.Com

Sub InsertBlankRows()

Dim rng As Range
Set rng = Selection

Application.ScreenUpdating = False

Do Until IsEmpty(ActiveCell) = True
Rows(ActiveCell.Row + 1).Insert
ActiveCell.Offset(2, 0).Select
Loop

Application.ScreenUpdating = True

End Sub

[/stextbox]

If you want more Excel and VBA tips then sign up for my monthly Newsletter where I share 3 Excel 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 Mondays Series or the example worksheets you can do so by clicking on the links below.

How To Excel At Excel – Macro Mondays Blog Posts.

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 with Excel FAST.