Hello Excellers and welcome back to another #MacroMonday #Excel blog post in my Excel 2020 Macro series. Today, I will show you how to hide any empty rows in your Excel spreadsheet.
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.
Watch The 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 These Locations?.
Well, if you save the code in your Personal Macro workbook it will be available in any Excel workbooks. 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 very useful macro which can be used over and over again. I therefore still want to save it 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
I want to use this code over and over again so I will choose at this time to save it in my Personal Macro Workbook.
Preparing To Write The Code
First, I need to start off the process 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 HideEmptyRows. 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 of code
[stextbox id=’info’]
Sub HideEmptyRows()
End Sub [/stextbox]
Disable Alerts and Screen Updating.
The first piece of code will disable any and alerts and screen updating. Not only will this help the code run much faster, but it also prevents screen flickering and makes the code look more professional. This also prevents and unnecessary pop-up messages that the user does not need to see.
[stextbox id=’info’]
Application.DisplayAlerts = False
Application.ScreenUpdating = False
[/stextbox]
Using The For Next Statement.
Next, we use the For Next statement. We can use this to repeat a set of statements a specified number of times. In this example, we set to loop through the rows 1 to 1000 on the active sheet. If the row is blank then its status is set to hidden. This statement is looped until all rows of 1 to 1000 have been completed.
[stextbox id=’info’]
For i = 1 To 1000
With ActiveSheet
If Application.CountA(.Rows(i)) = 0 Then .Rows(i).Hidden = True
End With
Next
[/stextbox]
Enable Alerts and Screen Updating.
Next we need to reinstate or enable the display alerts and screen updating. This resets these features we disabled before our code began to run.
[stextbox id=’info’]
Application.DisplayAlerts = True
Application.ScreenUpdating = True
[/stextbox]
Ending The Macro.
Once the worksheet has been copied the code ends with the End Sub statement. This was already input by default by Excel when we began writing the macro. your Empty Excel rows are now hidden.
[stextbox id=’info’]
End Sub
[/stextbox]
What Next? Want More Tips?
So, if you want more top 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.