Most likely, Excel sheet holds useless values and data that you don’t need for any purpose, that’s why people often hide them to make data understandable. Hiding many rows and columns will not make them visible for the time, however once in a while, you would have to delete hidden rows in Excel.
It simply does not make any sense to keep a part of the data hidden on the sheet because it is no longer needed. You have to delete it as soon as possible because it can only increase the file size and make your file worthless. Deleting those hidden rows and columns would not be an easy task.
Well, Excel has something for everyone and for this function we will use some built-in features that help in finding and deleting hidden rows. Without further ado, let’s get started:
Delete Hidden Rows in Excel with Inspect Document
For this procedure, let’s follow the steps given below:
- Click on the File tab given on the upper left side.
- Choose the Info tab from the pop-up menu and click on “Checks for Issues” and then “Inspect Document.”
- Now, you will see a menu that appears as “Document Inspector.”
- Click on the Inspect button.
- Choose the “Remove All” option from the “Hidden Rows and Columns” menu.
- Now, you will notice that all the hidden rows and columns are deleted.
Delete Hidden Rows in Excel with VBA
Manually, you can unhide and delete a few hidden rows. Using VBA is the best deal when you often have to repeat this function or when you are having a large data set containing a large number of hidden rows. With VBA coding, you can automate this function for ease.
Let’s understand how you can use VBA to delete hidden rows in Excel:
Deleting Rows from Entire Worksheet
Although you can even check the whole sheet from the last row and last column by using the VBA code check, however, it would be just a waste of time. Therefore, you can check the used range and then see the used range for hidden rows.
Below is the VBA code you can use to delete all the hidden rows in the used range:
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 SubUsing the above-mentioned VBA code, you can find the last row’s number in the used range and then that row number assigns to the variable ‘LastRow’. For Next Loop, this last row number would then be used that begins from the last row and checks if it is hidden or not. The entire row would be deleted if it is hidden, whereas if it is not hidden the code will not do anything to the row and moves to the next one. Using this code, you will be able to check the entire row and delete it if there is a hidden row found. Suppose you need to remove all the hidden columns in a used range then the following code will be useful:
Sub DeleteHiddenColumns()
Dim sht As Worksheet
Dim LastCol as Integer
Set sht = ActiveSheet
LastCol = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column
For i = LastCol To 1 Step -1
If Columns(i).Hidden = True Then Columns(i).EntireColumn.Delete
Next
End SubThis code will function similarly to the way above code works. The difference is just the rows and columns. If the last column number is given in the used range, then it will use the loop to reach from the last column to the first column and then deletes all the hidden columns. On the other hand, when you need a code that deletes all the hidden rows and columns, here is the code for this:
Sub DeleteHiddenRowsColumns()
Dim sht As Worksheet
Dim LastRow as Integer
Dim LastCol as Integer
Set sht = ActiveSheet
LastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row
LastCol = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column
For i = LastRow To 1 Step -1
If Rows(i).Hidden = True Then Rows(i).EntireRow.Delete
Next
For i = LastCol To 1 Step -1
If Columns(i).Hidden = True Then Columns(i).EntireColumn.Delete
Next
End SubThis VBA code is a combination of both rows and columns that works similarly. Here, rather than using one loop, you will notice two separate loops in which it first checks for rows and then columns. Likewise, it will delete all the hidden rows and then columns.
