This blog post shows you how to hide blank rows in Excel dataset using VBA.
Blank rows are not good in any Excel dataset and may create trouble while creating reports.
Assume we have a dataset that contains some blank rows.
You should remove or hide these blank rows so that they will not cause further trouble.
Blank rows are quickly removed or hidden if they are in small numbers.
However, if there are too many blank rows, we will have to perform some tasks to speed up the process.
So in this tutorial, we will use a VBA code that will hide all the blank rows quickly or in less than a second.
Hide Blank Rows in Excel – Entirely Blank Rows
We are using here the dataset below, which has blank rows within it. Here notice that the selected rows are entirely blank.

Now we will apply a VBA code here to hide these blank rows.
To apply the VBA code here, use the following procedure step by step.
First of all, click on the ‘Developer’ tab in the ‘Ribbon’ area and then go to the ‘Visual Basic’ tab.

When you click on the ‘Visual Basic’ tab, you will get the Visual Basic for Application window.

Go to the ‘Insert’ tab and select the ‘Module’ item from the drop-down list.
A new module will be inserted here, see the image below:-

Module – this is the place where you will write your VBA code.
Here we are going to write a VBA code to hide the blank rows within the dataset.
First of all, find the range, where our blank rows are existing.
Here, we will use the range “A1:E12”, where blank rows are existing as shown in the above image.
Now we will write the VBA code here to hide all the blank rows within the dataset.
Write the VBA code as shown in the image below.

With range “A1:E12” use ‘SpecialCells’ property.
Then start parentheses ( ‘(‘ ) and select (xlCellTypeBlanks) from the listed option.

And then write the rest of the code as shown below in the image:-

After completing the VBA code, execute the VBA code by using the ‘Run’ button from the VBA window or pressing ‘F5’ from the keyboard.
See that all the bank rows are hidden now, and if you want to unhide them, you can easily do it.
Go to the ‘Home’ tab and go-to ‘Format’, tab in the ‘Cells’ group, and click in the drop-down here.
Under the ‘Format’ tab drop-down, select ‘Hide & Unhide’ under the ‘Visibility’ option and again select ‘Unhide Rows’ here.

This is the example where blank rows are completely blank, so easy to select and hide.
But if the rows are partially blank within in dataset not fully.
Then the above VBA code is not going to work here.
Hide Blank Rows in Excel – Partially Blank Rows
In this situation, we need to use different VBA codes, which will not delete the partial blank rows.
See the dataset below where we have some rows are partially and some are a complete blank.

When we used the last method, we used the cell type as blank and then selected the entire row.
This will select the entire blank row and then we can hide this.
But in the second dataset, if we select the blank cells and then select the entire blank row.
The partially blank will be hide also, which is not correct.
So here we will create a different VBA code.
Here we will use the COUNTA function which will help us to find the complete blank rows.
If the rows are entirely blank, COUNTA will output zero, and if the rows are partially blank, COUNTA will output one.
See in the image below, where the complete blank row has a ‘0’ result.
See another row has resulted as 2.

So in this situation, the VBA code will calculate the total cell count of each row.
This VBA code hides a row if it has a count equal to zero, which is the case in this situation. Rows that count more than ‘0’ will not be hidden.
See the image below:-

Or you can copy the code from below
Sub Hide_Blank_Rows_02() Application.DisplayAlerts = False Application.ScreenUpdating = False For i = 1 To 100 ‘(use the dataset range here) With ActiveSheet If Application.WorksheetFunction.CountA(Rows(i)) = 0 Then Rows(i).Hidden = True End With Next i Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub |
I hope you find this tutorial useful.
If you want to learn more about this, please visit Microsoft Office Support.
Please feel free to put your comments or suggestion in the comment box below.
I hope you find this tutorial useful.
Please feel free to put your comments or suggestion in the comment box below.
Thanks
More Related Post
5 Ways to Delete Blank Rows in Excel (With VBA Macro)
How to Change the Background Colors in Excel VBA
3 Quick Ways to Change the Font Color in Excel VBA
Copy and Paste in Excel VBA: 4 Easiest Way (For Beginners)
How to Split Cells Diagonally in Excel (Step by Step Guide)
How to Remove Formulas and Keep the Values in Excel (4 Quick Ways)
3 Easy Ways to Select the Visible Cells Only In Excel
How to Highlight Every Other Row or Column in Excel
How to Insert Blank Rows after Every (2nd or 3rd or nth) Row in Excel
How to Wrap Text in Excel – 4 Easiest Method
3 Quick Ways to Move or Copy a Worksheet into Another Workbook