How to Delete Hidden Rows in Excel?

Sometimes while working in Microsoft Excel, we may have to deal with a lot of hidden rows or columns. Hidden data may be unavailable as well as unnecessary. In this article, we will demonstrate to you how to delete hidden rows in Excel.

1. Utilizing the ‘Inspect Document’ Option to Delete Hidden Rows in Excel

The ‘Inspect Document’ option is an effective way to hide rows. It deletes hidden rows from the entire workbook. So, this method is not applicable if we want to delete rows just from a single worksheet. We have to use VBA which we will discuss in the latter part of this article.

The following dataset consists of sales data. For your convenience, in this method, first, we will hide rows that are highlighted. After this, we will delete the highlighted rows. Let’s see the process of hiding the rows first:

Here, to hide the highlighted rows, select the rows.
Do a right-click on the cell index number.
Then, click on the option Hide.

So, we can see the highlighted rows are not visible.

Now we will follow some steps to delete these hidden rows.

Firstly, got to the File option from the ribbon.

Secondly, select the Info section. Go to the ‘Inspect Workbook’. From the drop-down select the ‘Inspect Document’ option.

A box like this will appear. Select Yes in this case.

Then from the Document Inspector window click on the option Inspect.

After that, in the new window scroll down. Select the option Remove All for the Hidden Rows and Columns section.

Finally, we can see that the hidden rows are not present anymore. The below image shows the uninterrupted sequence of row numbers after deleting the hidden rows.

Note:

We cannot undo the deleted rows after this process. So, you must keep the backup of the data if you don’t want to delete them permanently.

2. Adding a Temporary Column to Delete Hidden Rows

Another approach is to delete hidden rows by adding a temporary column. We will use filtering to apply this method. Suppose, in the following dataset we just want to keep the sales data of the location New York and Boston. We will delete the rest of the rows. Just follow the simple steps to execute this:

In the beginning, select the entire dataset (B4:G14).

Go to the Sort & Filter option. From the dropdown click on the option Filter.

As a result, we will see a dataset like this with filtering options at the column head.

Now, go to the filtering drop-down of the title Location. Just select the options New York and Boston.

Then, press OK.

Here, we will get only the sales data for New York and Boston. Other rows are hidden now.

Next, add a new column. Name it Temporary. Enter the value 0 in cell H5.
Drag the Fill Handle tool.

We get the value 0 for all the rows.

Select the entire dataset (B4:B15) again.

Then, go to the Sort & Filter drop-down. Select the option Filter.

Here all the hidden data are now visible. We can see 0 only in the rows that we want to keep.

We will select the entire dataset (B4:G15) again.

Go to Sort & Filter. From the drop-down select the option Filter.

We can see the filtering drop-downs in the title bars of the dataset.

Go to the drop-down option in the ‘Temporary’ column.
Here we will deselect option 0.
Click OK.

So, we get the rows that we have to delete.

Select all the rows. Do a right-click and click on the option Delete Row.

All the rows without the value 0 are now deleted.

Now go to the drop-down of the Temporary column. Select option 0.
Click OK.

Finally, we have only the dataset for the cities of New York and Boston.

3. Using VBA Code to Delete Hidden Rows in Excel

Using VBA is an advanced method to delete hidden rows. We can do it for two special cases. One is deleting rows from a single Worksheet. Another is to delete rows from a specific range of a dataset.

3.1. Deleting Hidden Rows from Entire Worksheet

Suppose, we have the following dataset of sales. In our experimental dataset, the highlighted rows are hidden. We will delete these rows using VBA.

After hiding the highlighted rows or database will look like the below image. We can see that the row index number is not consecutive.

It should be noted that if we do not have the Developer tab in our Excel, we have to activate the Developer tab to create macro-enabled content.

Firstly, we will see how to activate the Developer tab. Just follow the instructions given below.

Go to the File option in the top-left corner of our Excel.

Next, select the Options.

Then, a new window will come. Select the option Customize Ribbon from the available options.

After that, select the Developer option and click OK.

Finally, we can see the Developer tab in our Excel.

Now we will use the Developer tab to create macro-enabled content. Let’s see how we can do this in the following steps.

Go to the Developer tab. Select the Visual Basic option.

Here, a new window will open. From the window select the Insert tab. From the drop-down, select the Module option. We will get a new module named Module-1.

Select the option Module-1. A blank window will open. Insert the following code in the blank window.

Sub DeleteHiddenRows()
Dim sht As Worksheet
Dim LastRow
Set sht = ActiveSheet
LastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row
For i = LastRow To 1 Step -1
If Rows(i).Hidden = True Then Rows(i).EntireRow.Delete
Next
End Sub

We will Click on the run option that we can see in the below image. We can press F5 as well to run the code.

As a result, we can see that we have deleted all the hidden rows.

3.2. Removing Hidden Rows from Specific Range

In this example, we will use VBA to delete the hidden rows from a specific range. We will continue with our earlier dataset for this example too.

Assume, our range is (B4:G9). So, we will not delete hidden rows after row number 10. That’s why we are using two colors to highlight the hidden rows.

Now from the Developer tab go to the code window.
Insert the following code there:

Sub DeleteHiddenRows()
Dim sht As Worksheet
Dim Rng As Range
Dim LastRow As Integer
Dim RowCount As Integer
Set sht = ActiveSheet
Set Rng = Range("B4:G9")
RowCount = Rng.Rows.Count
LastRow = Rng.Rows(Rng.Rows.Count).Row
For i = LastRow To LastRow - RowCount Step -1
If Rows(i).Hidden = True Then Rows(i).EntireRow.Delete
Next
End Sub

We will Click on the run option that we can see in the below image. We can press F5 as well to run the code.

Finally, we can see that the hidden rows within the range (B4:G9) are deleted whereas hidden rows after row number 10 are still present.

Download Practice Workbook

You can download the practice workbook from here.

Deleting Hidden Rows.xlsx

Conclusion

In the end, we have gone through the different methods to delete rows in this article. To practice yourself download the practice workbook added with this article. If you feel any confusion just leave a comment in the below box. We will try to answer as soon as possible. Stay tuned with us for more interesting solutions to Microsoft Excel problems.

<< Go Back to Delete Multiple Rows Based on Condition | Delete Rows | Rows in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!