Hello Excellers welcome again to some more #macromondays fun. Today let’s look at automating a very laborious task that one of my colleagues had to carry out manually every week, they had to insert a blank row after every 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.
Let’s get coding!
What Does The Macro Do?
This Macro will insert a blank row after every row in the selection of rows.
How Does It Work?
FIRST. Open Visual Basic – by hitting ALT +F11 or Developer Tab – Visual Basic | Insert a new module
Step 1. We need to declare three variables.
Dim rng As Range
Dim CountRow As Integer
Dim i As Integer
This simply creates memory containers in Excel to store the range of cells selected by the user and the number of rows that well need to be inserted.
Step 2. The range of cells is set by the users selection, and the number of rows is stored to be used
Step 3. The For……Next looping method is used to loop through the first row in the range selection and insert a new entire row
Step 4. The new row is offset by 2 rows and 0 columns in the range
Step 5. This is repeated until the last row of the selected range, and the Macro ends.
Step 6. Test your Macro!!!
Want To Copy The Code?
If you want to see all of my blog posts in the Macro Mondays Series you can find them all in the link below. Why not book mark it?, Yes it is updated EVERY Monday.
You can also download the corresponding example workbook that contains the Macro code by clicking on the download button below.