How to Delete Hidden Rows in Excel (3 Methods)

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.


Download Practice Workbook

You can download the practice workbook from here.


3 Methods to Delete Hidden Rows in Excel

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

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

‘Inspect Document’ Option to Delete Hidden Rows in Excel

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

‘Inspect Document’ Option to Delete Hidden Rows in Excel

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

‘Inspect Document’ Option to Delete Hidden Rows in Excel

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

  • Firstly, got to the File option from the ribbon.

‘Inspect Document’ Option to Delete Hidden Rows in Excel

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

‘Inspect Document’ Option to Delete Hidden Rows in Excel

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

‘Inspect Document’ Option to Delete Hidden Rows in Excel

  • 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 can not 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 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:

Adding Temporary Column to Delete Hidden Rows

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

Adding Temporary Column to Delete Hidden Rows

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

Adding Temporary Column to Delete Hidden Rows

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

Adding Temporary Column to Delete Hidden Rows

  • Now, go to the filtering drop-down of the title Location. Just select the option New York and Boston.
  • Then, press OK.

Adding Temporary Column to Delete Hidden Rows

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

Adding Temporary Column to Delete Hidden Rows

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

Adding Temporary Column to Delete Hidden Rows

  • We get the value 0 for all the rows.

Adding Temporary Column to Delete Hidden Rows

  • Select the entire dataset (B4:B15) again.

Adding Temporary Column to Delete Hidden Rows

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

Adding Temporary Column to Delete Hidden Rows

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

Adding Temporary Column to Delete Hidden Rows

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

Adding Temporary Column to Delete Hidden Rows

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

Adding Temporary Column to Delete Hidden Rows

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

Adding Temporary Column to Delete Hidden Rows

  • 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 city New York and Boston.


3. Using VBA to Delete Hidden Rows in Excel

Using VBA is an advanced method to delete the 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 Delete 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.

Delete Hidden Rows from Entire Worksheet

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

Delete Hidden Rows from Entire Worksheet

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.

Delete Hidden Rows from Entire Worksheet

  • Next, select the Options.

Delete Hidden Rows from Entire Worksheet

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

Delete Hidden Rows from Entire Worksheet

  • After that, select the Developer option and click OK.

Delete Hidden Rows from Entire Worksheet

  • 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 Hidden Rows Delete 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.

Hidden Rows Delete from Specific Range

  • 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.

Hidden Rows Delete from Specific Range

  • 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.

Hidden Rows Delete from Specific Range

  • 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.


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.

Mukesh Dipto

Hello, everyone. Myself Mukesh Saha Dipto. I completed my studies in Engineering. I am currently employed with SOFTEKO as a technical content writer. Working in a digital platform and learning about the market has always been a dream of mine. So, I have chosen to work here to explore in a new environment. As a result, I've chosen to work here in order to get experience in a new setting. Sports, travel, and meeting new people are some of my favorite pastimes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo