How to Delete Hidden Rows or Columns in Excel?

by

Excel allows hiding rows in columns in case you don’t want to delete them, but also don’t want them to be visible in your worksheet.

And in case you need these rows that have been hidden in the future, you can easily unhide them.

While this is a great feature, hidden rows and columns is something that troubles a lot of Excel users who inherit these Excel files (maybe from a colleague or manager)

In many cases, it would be a good idea to delete these hidden Rows and columns in Excel.

While one obvious way to delete hidden rows/columns would be to first unhide these and then manually delete them, it’s not the most efficient way.

In this short tutorial, I will show you two simple ways you can use quickly delete hidden rows and columns in Excel.

And no, you won’t have to unhide these rows to delete them. The methods I show you here will take care of it.

Delete Hidden Rows and Columns Using Inspect Document

The easiest way to quickly delete all the hidden rows and hidden columns is by using the Inspect Document option in Excel.

This option goes through your entire workbook, and checks for various parameters – including a check for hidden rows and columns.

And in case there are any hidden rows or hidden columns, it allows you to quickly delete all of these with a single click.

Below are the steps to use the Inspect Document feature to delete hidden rows in Excel:

  1. Click the File tab
Click the file tab
  1. In the options that show up on the left, click on Info
Click the Info option
  1. Within the Info options, click on the ‘Check for Issues’ option
Click on Check issues for option
  1. In the options that show up in the drop-down, click on the ‘Inspect Document’ option
Click on Inspect documents
  1. If you see a prompt that asks you to save your Excel workbook, click on Yes. If you haven’t saved your workbook, it would show you the prompt to first save the file
  2. In the Document Inspector dialog box, click on the ‘Inspect’ button. Doing this will quickly inspect your document and check it using the criteria as given in the ‘Document Inspector’ dialog box
Click on Inspect button
  1. Scroll down to the hidden rows and columns section. If there are any hidden rows and columns in your workbook, you will see the number here
Hidden Rows and columns listed in the document inspector
  1. Click on the ‘Remove All’ button

The above steps would instantly remove all the hidden rows and hidden columns from your workbook.

Note that it will not tell you which rows and columns are hidden, but only show you the total number of hidden rows and hidden columns. So make sure you are comfortable deleting all these hidden rows and columns and would not need these later.

Inspect document option doesn’t allow you to choose any specific worksheet or a range of cells from which you want to delete hidden rows and columns.

It is a workbook-wide operation that would delete all the hidden rows and all the hidden columns from the workbook.

It also does not allow you the option to choose whether you only want to delete hidden rows and not columns (or vice versa)

Pro Excel Tip: It’s always a good idea to create a backup copy of your workbook before performing a workbook-wide operation such as deleting hidden rows and columns. Since this change is irreversible, in case you need any of the data from these hidden rows or columns, you would at least have a backup to fall back on

Also read: How to Rearrange Rows In Excel (4 Easy Ways)

Delete Hidden Rows and Columns Using VBA

Another way you can quickly delete all the hidden rows or hidden columns from your worksheet/workbook is by using VBA.

While the Inspect Document option doesn’t give you any flexibility when it comes to deleting hidden rows, with VBA you can choose to only delete hidden rows from a specific range of cells, a specific worksheet, or the entire workbook.

VBA also allows you to go through the content of the rows, and delete only specific hidden rows while leaving the others as is.

VBA Code to Delete Hidden Rows from Active Sheet Only

Below is the VBA code that would delete all the hidden rows in the active sheet.

To use this code, first, select the worksheet from which you want to delete these hidden rows, and then run this code.

'Code developed by Steve Scott from https://spreadsheetplanet.com/
Sub Delete_Hidden_Rows()
Dim sht As Worksheet
Dim LastRow As Integer
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

The above code uses the concept of Used Range, where it first identifies the last used row in the worksheet.

This is more efficient as instead of going through all the rows in the sheet, it will only go through the used range which would be a lot less (so the code would be a lot faster)

Once the last row of the Used Range is identified, it goes through all the rows above it one by one (using the For Next loop) and checks whether there are any hidden rows in it.

Any row that is identified as hidden is deleted, and all the other rows are left as is.

Important: Remember that the changes done by the VBA code are irreversible. So always make a backup copy before running the VBA code

Also read: How to Remove Blank Columns in Excel? (Formula + VBA)

VBA Code to Delete Hidden Columns from Active Sheet Only

And below is the VBA the code that would delete all the hidden columns from the active sheet.

'Code developed by Steve Scott from https://spreadsheetplanet.com/
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 Sub

This again uses the same concept where it identifies the last column in the Used Range and then starts going towards the left, checking each column for its hidden property.

If a column is hidden, it will be deleted; else, it will be left as is.

Also read: How to Delete Multiple Rows in Excel?

VBA Code to Delete Hidden Rows/Columns from All Sheets

Below is the VBA code that would go through all the worksheets in the active workbook and check for hidden rows.

If it finds any, in any of the worksheets, it would be deleted.

'Code developed by Steve Scott from https://spreadsheetplanet.com/
Sub Delete_Hidden_Rows()
Application.DisplayAlerts = False
Dim sht As Worksheet
Dim LastRow As Integer
Set CurrentSheet = ActiveSheet
For Each sht In Worksheets
sht.Activate
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 i
Next sht
CurrentSheet.Activate
Application.DisplayAlerts = True
End Sub

Once the code is done cycling through the worksheets, it would come back to the original worksheet that was active when the code was executed.

And below is the VBA code that would delete all the hidden columns from all the worksheets in the active workbook.

'Code developed by Steve Scott from https://spreadsheetplanet.com/
Sub Delete_Hidden_Columns()
Application.DisplayAlerts = False
Dim sht As Worksheet
Dim LastCol As Integer
Set CurrentSheet = ActiveSheet
For Each sht In Worksheets
sht.Activate
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 i
Next sht
CurrentSheet.Activate
Application.DisplayAlerts = True
End Sub

I’ve tried to give you a framework that you can use with VBA to delete hidden rows or hidden columns in your workbook.

You can also modify the code to delete specific hidden rows or columns based on any criteria (such as only deleting hidden rows that contain a specific text or do not contain a specific text).

In this tutorial, I showed you two ways to quickly delete hidden rows and hidden columns in Excel. If you want to delete all the hidden rows in one go from the entire workbook, you can use the inspect document feature.

And case you need a little more control over deleting the hidden rows and columns, then you can use the VBA code.

Other Excel tutorials you may also find useful:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment