Removing Hidden Rows in Excel

Hiding rows or columns is a common task in Excel, but when it comes to deleting hidden rows or columns, it can be time-consuming. Luckily, Excel has a built-in feature to quickly find and remove hidden rows or columns. Here are some simple steps to follow:

Method 1: Remove Hidden Rows or Columns using Inspect Document

1. Click on the “File” tab.
2. Select “Info” from the pop-up menu and then click on “Check for Issues” and then “Inspect Document”.
3. Excel opens the “Document Inspector” menu. Click on the “Inspect” button.
4. In the “Hidden Rows and Columns” section, click on “Remove All”.
5. All hidden rows or columns in Excel have now been removed.

Method 2: Remove Hidden Rows or Columns using VBA

You can also use VBA to delete hidden rows or columns. Here are two codes to delete hidden columns and rows respectively:

Sub delete_hidden_columns()
Dim ws As Worksheet
Dim iCol As Integer
Set ws = ActiveSheet
iCol = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column
For i = iCol To 1 Step -1
If Columns(i).Hidden = True Then Columns(i).EntireColumn.Delete
Next
End Sub
‘————————————————-
Sub delete_hidden_rows()
Dim ws As Worksheet
Dim iRow As Integer
Set ws = ActiveSheet
iRow = ws.UsedRange.Columns(ws.UsedRange.Rows.Count).Row
For i = iRow To 1 Step -1
If Rows(i).Hidden = True Then Rows(i).EntireRow.Delete
Next
End Sub

Using VBA, you can quickly delete hidden rows or columns within the used range. Simply choose the relevant code and run it.