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.

Hide Blank Rows in Excel
Hide Blank Rows in Excel_01

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.

Hide Blank Rows in Excel
Hide Blank Rows in Excel_02

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

Hide Blank Rows in Excel
Hide Blank Rows in Excel_03

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:-

Hide Blank Rows in Excel
Hide Blank Rows in Excel

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.

Hide Blank Rows in Excel
Hide Blank Rows in Excel

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:-

Hide Blank Rows in Excel
Hide Blank Rows in Excel

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.

Hide Blank Rows in Excel
Hide Blank Rows in Excel

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.

Hide Blank Rows in Excel

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)

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

Leave a Comment

Your email address will not be published.