How to Hide Blank Rows in Excel Using VBA Macro

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

Hide Blank Rows in Excel

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)