Delete hidden rows and columns in a worksheet
This tutorial shows how to delete all hidden rows and columns in a specific worksheet at once using VBA
METHOD 1. Delete hidden rows and columns in a worksheet
VBA
Sub Delete_Hidden_Rows_Columns()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Application.ScreenUpdating = False
Set ws = Worksheets("Sheet1")
ws.Activate
ws.Activate
For numcol = 1 To Columns.Count
If Columns(numcol).EntireColumn.Hidden = True Then
Columns(numcol).EntireColumn.Delete
Columns(numcol).EntireColumn.Delete
Else
End If
Next numcol
For numrow = 1 To Rows.Count
If Rows(numrow).EntireRow.Hidden = True Then
Rows(numrow).EntireRow.Delete
Rows(numrow).EntireRow.Delete
Else
End If
Next numrow
Application.ScreenUpdating = True
End Sub
ADJUSTABLE PARAMETERS
Worksheet Selection: Select the worksheet in which you want to delete all hidden rows and columns by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Worksheet Selection: Select the worksheet in which you want to delete all hidden rows and columns by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
ADDITIONAL NOTES
Note 1: This macro will loop through each column and row in a specific worksheet and delete all hidden columns and rows.
Note 1: This macro will loop through each column and row in a specific worksheet and delete all hidden columns and rows.
EXPLANATION
This tutorial shows how to delete all hidden rows and columns in a specific worksheet at once by using VBA.
The macro will loop through each row and column in a specific worksheet and identify if the row or column is hidden. If it identifies that a row or column is hidden it will delete it. Please note that given the macro will be looping through each cell in a specific worksheet it might take a while until it can complete the task.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Delete hidden rows and columns in a workbook | How to delete all hidden rows and columns in a workbook at once | |
| Delete hidden rows in a worksheet | How to delete only the hidden rows in a specific worksheet at once |