Sometimes, while working on Excel data, users hide multiple rows and columns that they don’t want to be visible, and later, after making the data ready, deleting those multiple hidden rows and columns within the data is a time-consuming task.
To overcome this, Excel has an inbuilt function to find and delete hidden rows and columns in one go. Here are some easy and quick steps to delete all hidden rows or columns in Excel.
Delete All Hidden Rows or Columns in Excel using the Inspect Document
The primary purpose of the document inspector is to help you identify and remove hidden or personal data from your workbook. It ensures that confidential information is not unintentionally shared.
- First, click on the “File” tab.
- After that click on the “Info” from the pop-up menu and then click on “Check for Issues” and then “Inspect Document”.
- Once you click on “Inspect Document”, Excel opens the “Document Inspector” menu.
- Now, you just need to click on the “Inspect” button.
- In the end, click on “Remove All” in the “Hidden Rows and Columns” selection.
- At this point, all the hidden rows or columns in Excel have been removed.
Points to Consider While Using Inspect Document
- It removes all hidden rows and columns from your workbook and any data, formulas, or formatting.
- Any calculation might be affected if the hidden rows and columns contained data used in calculations, summaries, or pivot tables.
Delete All Hidden Rows or Columns using VBA
Using a VBA code is one of the best ways to delete the hidden rows and columns. You can choose how to delete these rows and columns with a VBA code.
Sub DeleteHiddenColumns()
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 DeleteHiddenRows()
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
1. Delete Rows and Columns Only IF there’s No Data in it
The code below deletes hidden rows with no values and highlights rows with values in the active worksheet. First, it checks the entire worksheet for any data.
If data exists, it loops through each row from bottom to top, deleting hidden rows without values and unhiding and highlighting them with values.
A message box confirms the completion and informs if no visible data was found.
Sub DeleteHiddenRowsWithNoValues()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim cell As Range
Dim rowHasValue As Boolean
Dim hasVisibleData As Boolean
' Set the active worksheet
Set ws = ActiveSheet
' Find the last row with data in the worksheet
lastRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row
' Check if there is any visible data in the worksheet
hasVisibleData = False
For Each cell In ws.UsedRange
If Not cell.EntireRow.Hidden And Not IsEmpty(cell.Value) Then
hasVisibleData = True
Exit For
End If
Next cell
' Loop through each row from bottom to top to avoid skipping rows
For i = lastRow To 1 Step -1
If ws.Rows(i).Hidden Then
rowHasValue = False
' Check each cell in the row to see if there is any value
For Each cell In ws.Rows(i).Cells
If Not IsEmpty(cell.Value) Then
rowHasValue = True
Exit For
End If
Next cell
' If the row has no value, delete it
If Not rowHasValue Then
ws.Rows(i).Delete
Else
' If the row has a value, unhide it and highlight the first cell
ws.Rows(i).Hidden = False
ws.Cells(i, 1).Interior.Color = RGB(255, 255, 0) ' Highlight the first cell in yellow
End If
End If
Next i
' Optional: Display a message indicating the process is complete
MsgBox "Hidden rows with no values have been deleted. Rows with values have been unhidden and highlighted."
' If no visible data found, show a message
If Not hasVisibleData Then
MsgBox "There is no visible data in the worksheet."
End If
End Sub
Like the above code, the code deletes hidden columns with no values and highlights columns with values in the active worksheet.
It checks the entire worksheet for any data. If visible data, it loops through each column from right to left, deleting hidden columns without values and unhiding and highlighting hidden columns with values.
Sub DeleteHiddenColumnsWithNoValues()
Dim ws As Worksheet
Dim lastCol As Long
Dim i As Long
Dim cell As Range
Dim colHasValue As Boolean
Dim hasVisibleData As Boolean
' Set the active worksheet
Set ws = ActiveSheet
' Find the last column with data in the worksheet
lastCol = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column
' Check if there is any visible data in the worksheet
hasVisibleData = False
For Each cell In ws.UsedRange
If Not cell.EntireColumn.Hidden And Not IsEmpty(cell.Value) Then
hasVisibleData = True
Exit For
End If
Next cell
' Loop through each column from right to left to avoid skipping columns
For i = lastCol To 1 Step -1
If ws.Columns(i).Hidden Then
colHasValue = False
' Check each cell in the column to see if there is any value
For Each cell In ws.Columns(i).Cells
If Not IsEmpty(cell.Value) Then
colHasValue = True
Exit For
End If
Next cell
' If the column has no value, delete it
If Not colHasValue Then
ws.Columns(i).Delete
Else
' If the column has a value, unhide it and highlight the first cell
ws.Columns(i).Hidden = False
ws.Cells(1, i).Interior.Color = RGB(255, 255, 0) ' Highlight the first cell in yellow
End If
End If
Next i
' Optional: Display a message indicating the process is complete
MsgBox "Hidden columns with no values have been deleted. Columns with values have been unhidden and highlighted."
' If no visible data found, show a message
If Not hasVisibleData Then
MsgBox "There is no visible data in the worksheet."
End If
End Sub
2. Delete Hidden Rows and Columns in All Worksheets
The code below loops through every worksheet in the workbook, finding and deleting all hidden rows and columns, ensuring the entire workbook is cleaned up.
It loops for rows from the bottom up and columns from right to left to avoid skipping hidden rows and columns.
Sub DeleteHiddenRowsAndColumnsInAllSheets()
Dim ws As Worksheet
Dim lastRow As Long
Dim lastCol As Long
Dim i As Long
' Loop through each worksheet in the workbook
For Each ws In ThisWorkbook.Worksheets
' Find the last row and column with data in the worksheet
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
' Loop through each row from bottom to top to avoid skipping rows
For i = lastRow To 1 Step -1
If ws.Rows(i).Hidden Then
ws.Rows(i).Delete
End If
Next i
' Loop through each column from right to left to avoid skipping columns
For i = lastCol To 1 Step -1
If ws.Columns(i).Hidden Then
ws.Columns(i).Delete
End If
Next i
Next ws
MsgBox "Hidden rows and columns in all worksheets have been deleted."
End Sub
3. Delete Hidden Rows and Columns in a Specific Range
The below code deletes hidden rows or columns within a specified range in the active worksheet. It sets the range to check and then loops through each row/column in that range. If a row/column is hidden, it gets deleted.
Sub DeleteHiddenRowsInRange()
Dim ws As Worksheet
Dim rng As Range
Dim row As Range
' Set the active worksheet
Set ws = ActiveSheet
' Define the range to check for hidden rows
Set rng = ws.Range("A1:D20") ' Change the range as needed
' Loop through each row in the specified range
For Each row In rng.Rows
If row.Hidden Then
row.Delete
End If
Next row
MsgBox "Hidden rows within the specified range have been deleted."
End Sub
Sub DeleteHiddenColumnsInRange()
Dim ws As Worksheet
Dim rng As Range
Dim col As Range
' Set the active worksheet
Set ws = ActiveSheet
' Define the range to check for hidden columns
Set rng = ws.Range("A1:D20") ' Change the range as needed
' Loop through each column in the specified range
For Each col In rng.Columns
If col.Hidden Then
col.Delete
End If
Next col
MsgBox "Hidden columns within the specified range have been deleted."
End Sub
4. Delete Hidden Rows and Columns with Specific Text in a Cell
The below code deletes hidden rows and columns in the active worksheet if they contain specific text (“DeleteMe”). It checks each cell in hidden rows and columns for the text. If the text is found, the entire row or column is deleted.
Sub DeleteHiddenRowsAndColumnsWithText()
Dim ws As Worksheet
Dim lastRow As Long
Dim lastCol As Long
Dim i As Long
Dim cell As Range
Dim containsText As Boolean
' Set the active worksheet
Set ws = ActiveSheet
' Find the last row and column with data in the worksheet
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
' Loop through each row from bottom to top to avoid skipping rows
For i = lastRow To 1 Step -1
If ws.Rows(i).Hidden Then
containsText = False
' Check each cell in the row for specific text
For Each cell In ws.Rows(i).Cells
If InStr(cell.Value, "DeleteMe") > 0 Then ' Change condition as needed
containsText = True
Exit For
End If
Next cell
' If the row contains the specific text, delete it
If containsText Then
ws.Rows(i).Delete
End If
End If
Next i
' Loop through each column from right to left to avoid skipping columns
For i = lastCol To 1 Step -1
If ws.Columns(i).Hidden Then
containsText = False
' Check each cell in the column for specific text
For Each cell In ws.Columns(i).Cells
If InStr(cell.Value, "DeleteMe") > 0 Then ' Change condition as needed
containsText = True
Exit For
End If
Next cell
' If the column contains the specific text, delete it
If containsText Then
ws.Columns(i).Delete
End If
End If
Next i
MsgBox "Hidden rows and columns containing specific text have been deleted."
End Sub
5. Count the Number of Hidden Rows and Columns in the Worksheet or Workbook
Sub CountHiddenRowsAndColumns()
Dim ws As Worksheet
Dim lastRow As Long
Dim lastCol As Long
Dim i As Long
Dim hiddenRowCount As Long
Dim hiddenColCount As Long
' Set the active worksheet
Set ws = ActiveSheet
' Find the last row and column with data in the worksheet
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
' Initialize counts
hiddenRowCount = 0
hiddenColCount = 0
' Count hidden rows
For i = 1 To lastRow
If ws.Rows(i).Hidden Then
hiddenRowCount = hiddenRowCount + 1
End If
Next i
' Count hidden columns
For i = 1 To lastCol
If ws.Columns(i).Hidden Then
hiddenColCount = hiddenColCount + 1
End If
Next i
' Display counts in a message box
MsgBox "Hidden Rows: " & hiddenRowCount & vbCrLf & "Hidden Columns: " & hiddenColCount
End Sub
Below is a user-defined function that you can use to count the hidden rows or columns from a range.
=CountHiddenRows(A1:A100) ' adjust the range as needed
=CountHiddenColumns(A1:A100) ' adjust the range as needed
Function CountHiddenRows(rng As Range) As Long
Dim cell As Range
Dim hiddenCount As Long
hiddenCount = 0
For Each cell In rng.Rows
If cell.EntireRow.Hidden Then
hiddenCount = hiddenCount + 1
End If
Next cell
CountHiddenRows = hiddenCount
End Function
Function CountHiddenColumns(rng As Range) As Long
Dim cell As Range
Dim hiddenCount As Long
hiddenCount = 0
For Each cell In rng.Columns
If cell.EntireColumn.Hidden Then
hiddenCount = hiddenCount + 1
End If
Next cell
CountHiddenColumns = hiddenCount
End Function