Many Excel users always hide columns and rows that have data they do not need visible. They keep only the columns and rows with useful data visible. In cases where you have lots of these hidden rows and columns, it can be painful and tiresome finding them.
The post below will guide you on the different methods you can use to delete all hidden rows and columns in your Excel worksheet. Let's get started.
Deleting hidden rows and columns in Excel using the Document Inspector method
Thus, you can use an in-built method if you want to delete all the hidden rows and columns in your Excel workbook in one go. Just follow the following steps.
1. Click on the File tab in an open Excel workbook.
2. Select the Info option from the popup menu list on the left.
3. From the given options on the Info page, click the 'Check for Issues button.'
4. Select 'Inspect Document from the drop-down list to display a dialog box.
5. In the Document Inspector dialog box window, click Inspect button.
This prompt command will inspect your entire workbook and give you return information about the workbook.
- Scroll down to the Hidden Rows and Columns section.
Here you will see the number of hidden rows and columns found within your workbook.
- Click on the 'Remove All' button.
- Lastly, click the Close button.
Remember, this method will remove all hidden rows and columns from your entire workbook and not from your active worksheet.
Using the VBA Macro Method to delete hidden rows and columns in Excel
If you want to delete hidden rows or columns in a specific sheet or a specific range, using an Excel VBA macro code is best.
To use the Excel VBA macro to delete any hidden columns and rows in your worksheet, follow the steps below;
1. Open your Excel workbook and click on the DEVELOPER tab on the main menu ribbon.
2. Under the Code section, click on the 'Visual Basic' command. You can also press the ALT + F11 shortcut. To display a popup window.
3. Click on the Insert tab to display a drop-down list on the displayed Visual Basic Editor window.
4. Click on the option 'Module' to create a new module.
5. Paste the VBA code below into the displayed code window. Afterward, click the Save button.
6. Saving the code will return you to your current worksheet. From here, run the above macro.
7. Click the Run button.
The above code will first find out the last row number in the used range. You can delete any hidden rows using the code.
If you want to delete any hidden columns in the used range, you will use the code below.
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
Lastly, if you want a code that deletes all the hidden rows and columns, you will use the VBA code below.
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 Sub
If you often do delete rows and columns, you can add these codes to the personal macro workbook. It will allow you to access the codes anytime for all the workbooks on your computer.