Hello Excellers, today let’s look at a handy way to get rid of any blank rows on your Excel worksheet. We can do this with the click of a Macro. You may if you have used Excel for any amount of time, experienced the annoyance of extra blank rows in your Excel worksheet. They can cause issues with Pivot Tables (putting in those extra Blank lines- grr most annoying) and also when you are looking to go to the last active row or column they can take you way past where you want to be. This is because you are navigating your worksheet as those cells previously contained data. So, here is a handy Excel VBA macro to solve the problem.
I myself have this saved in my Personal Macro Workbook, so it is available to work in any workbook’s worksheet I need it to.
Download the Macro I Used In This Example Below
[wpdm_package id=’10246′]
Other Blog Posts You May Find Useful.
Enable The Developer Tab In Excel
Macro Mondays – How To Run An Excel Macro Automatically When You Open Your Excel Workbook
Macros Mondays – How To Attach A Macro To Command Button In Excel
How To Delete All Bank Rows Macro
In this Macro, we use the UsedRange property of the Activesheet object to define the range of cells to work with. This UsedRange will give us a range of cells that have been used to enter data. (Just like when you have deleted data out of your Pivot Table Data Source, which when you refresh your Pivot Table show up as Blank Values).
We can get Excel to begin a counter at the last row of the used range and check if the entire row is empty if it is then we can remove the row – excellent!!. We can keep looping through the delete and incrementing to the previous row used. Let’s see it working.
The Code Explained.
The Macro has 2 Variables that we declare.
1. an Object called MyRange. Our target range
2. is a Long Integer variable named RCount – this is the row counter which is incremental
3. The Macro uses the MyRange variable by filling it with the range of cells that have been used to enter data in the active sheet.
4. Next, Excel sets the incremental counter for the range MyRange.Rows.Count and end at 1 which is the first row of the chosen range. The Step -1 indicates we want to go backwards in the range one increment at a time; so Excel will begin at the last row of the chosen range until the first row of the range is reached.
5. The Macro at this stage will use the RCount variable as an index number for the MyRange rows. If the the cells in the row are empty then Excel deletes the whole row.
6. The last stage loops back to the counter and the next increment down.
Thanks, Excel! I hope you find this piece of code useful. Let me know in the comments below if you have used it.
Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.
