A lot of Excel users hide rows and columns when they have some data that they don’t need visible.
It’s a way to only keep useful data visible, and at the same time not have to delete the data you don’t need to be visible.
And, if you have lots of such hidden rows/columns, it could be a pain to find and delete these hidden rows and columns (in the case when you don’t need them).
In this tutorial, I’ll show you a couple of ways to easily delete hidden rows and columns in Excel.
There is an inbuilt method that allows you to delete all the hidden rows and columns in one go, and you can also use VBA macro codes in case you want to apply this to our selected range of cells.
As an Excel user, you may frequently hide rows in your worksheets to keep certain data out of view But over time, these hidden rows can accumulate and clutter your file, slowing down performance. So knowing how to delete hidden rows in Excel is an important skill
In this comprehensive guide, I’ll explain three simple methods to delete hidden rows in Excel, using built-in options as well as VBA macros. Whether you want to delete hidden rows from the entire worksheet or just a specific range, this tutorial will show you how.
Why Delete Hidden Rows in Excel?
Here are some key reasons you may want to delete hidden rows in Excel:
-
Remove clutter and reduce file size As mentioned, hidden rows take up space and can slow down your Excel file over time Deleting them periodically clears out the clutter
-
Prepare worksheet for distribution Before sharing an Excel file with colleagues, you may want to delete any hidden rows containing confidential or unnecessary data.
-
Start with clean slate: Deleting hidden rows gives you a fresh start if you want to restructure or reformat your worksheet.
-
Fix formatting issues: Sometimes hiding rows can cause inadvertent formatting problems in Excel. Deleting the hidden rows fixes this.
-
Refresh data: If hidden rows are disconnected from data sources or formulas, deleting them forces a fresh data pull when unhidden.
Method 1: Utilize the “Inspect Document” Option
The quickest way to delete all hidden rows in an Excel workbook is using the “Inspect Document” option:
-
Click the File tab and select Info.
-
Click Check for Issues > Inspect Workbook.
-
In the Document Inspector dialog box, click Inspect. This audits the entire workbook.
-
Scroll down to the Hidden Rows and Columns section. Click Remove All to delete hidden rows.
-
Save your changes when prompted. Note this action cannot be undone.
The Document Inspector provides other useful information too, like hidden worksheets or personal info. So take a moment to review its results.
This method deletes all hidden rows in the entire workbook. To target a specific sheet or range, use the methods below.
Method 2: Add a Temporary Helper Column
If you only want to delete hidden rows in a certain data range, here is a clever filtering technique:
-
Select your target data range and apply a filter.
-
Filter to only show the rows you want to keep visible. Other rows are now hidden.
-
Add a helper column titled “Temporary” and fill with a 0 value in visible rows.
-
Remove the filter to reveal all rows again. Hidden rows show blank in the helper column.
-
Reapply the filter and deselect 0 in the Temporary column. This hides rows to delete.
-
Delete all the hidden rows in one action.
-
Remove the filter and Temporary column when done.
This preserves any formulas or formatting in your data by selectively deleting rows. The Temporary column gives you precision control over which hidden rows get removed.
Method 3: Write a VBA Macro
For frequent or large hidden row deletions, VBA macros get the job done fast through automation. Here are two macro approaches:
Delete Hidden Rows in Entire Worksheet
This macro deletes hidden rows across the entire used range in a worksheet:
Sub DeleteHiddenRows() Dim sht As Worksheet Dim LastRow As Long Set sht = ActiveSheet LastRow = sht.UsedRange.Rows.Count For i = LastRow To 1 Step -1 If Rows(i).Hidden = True Then Rows(i).EntireRow.Delete End If Next iEnd SubIt loops from the last row to the first, deleting any hidden rows along the way. Adjust the workbook and worksheet references to target a specific location.
Delete Hidden Rows in Specific Range
To delete hidden rows only within a fixed range, use this version:
Sub DeleteHiddenRows() Dim sht As Worksheet Dim Rng As Range Dim LastRow As Long Dim RowCount As Long Set sht = ActiveSheet Set Rng = Range("A1:Z100") 'Set your range RowCount = Rng.Rows.Count LastRow = Rng.Rows.Count For i = LastRow To LastRow - RowCount Step -1 If Rows(i).Hidden = True Then Rows(i).EntireRow.Delete End If Next i End SubIt loops from the last row to the first within the specified range, deleting hidden rows. Update the range to your needs.
Tips for Deleting Hidden Rows in Excel
Here are some best practices as you work with deleting hidden Excel rows:
-
Double-check which rows are hidden before deleting. Accidental deletes could lose important data.
-
Be aware that deletes cannot be undone! Save a copy of the workbook first if unsure.
-
Sort rows before deleting to group hidden rows together at the bottom.
-
Filter by blank cells to quickly identify hidden rows for deletion.
-
Freeze panes on a large worksheet to keep column headers visible when scrolling through rows.
-
Use VBA error handling to stop macro execution if any errors occur during row deletion.
Now you have the details on how to delete those pesky hidden rows in Excel using the Inspector, filtering, or VBA macros.
As you become more skilled with these methods, you can clean up and optimize Excel workbooks in just minutes. A worksheet free of cluttered hidden rows allows you to focus on the essential data at hand.
So be proactive about deleting hidden rows on a routine basis. Refer back to this guide anytime you need a quick refresher on the steps. With practice, you’ll be able to leverage these techniques to boost productivity and keep your Excel files running smoothly.
Delete All Hidden Rows and Columns in Excel
If you want to delete all the hidden rows and columns in an entire workbook in Excel, you can use the method shown here.
Remember that it is going to remove these hidden rows and columns from the entire workbook and not from the active sheet only.
Below are the steps to delete all the hidden rows and columns from the workbook in Excel:
- Click the File option
- In the options on the left, click on ‘Info’
- Click on the ‘Check for Issues’ option
- Click on the ‘Inspect Document’ option. This will open the Document Inspector dialog box
- In the ‘Document Inspector’ dialog box, click on the ‘Inspect’ button. This will inspect the entire workbook and give you the information about the workbook
- Scroll down to the ‘Hidden Rows and Columns’ option. You will see that it shows the total number of hidden rows and columns that it has found in the workbook.
- Click on the ‘Remove All’ button
The above steps would delete all the hidden rows and columns in the workbook.
Note that you need to save this workbook before running the Document Inspector option. In case the workbook is not saved already, Excel would first force you to save it. Also, it will show you a warning prompt asking to save the file once, as the data changed by these steps can not be recovered.
In case there are no hidden rows and columns, you would see a green tick before the Hidden and Rows and Columns option (in the Document Inspector dialog box).
Apart from hidden rows and columns, the Document Inspector dialog box also gives you a lot of other useful information – such as the number of comments, or hidden worksheets, or embedded documents, etc.Note: You cannot undo the changes made by the Document Inspector. So make sure you have a backup copy of the original data (in case if you may need it in the future)
This is a great method if you want to delete all the hidden columns and rows for the entire workbook.
But in case you only want to delete it in a specific sheet or in a specific range, then you cannot use this option.
In that case, you can use the VBA method covered next.
Delete Hidden Rows and Columns using VBA
If you only have a few hidden rows and columns, it’s possible to unhide these manually and then delete it.
But if this is something you need to do quite often, or if you have a large data set with a large number of rows/columns that are hidden, it’s best to use VBA macro codes to automate this process.
Let’s look at different scenarios where you can use VBA to delete these hidden rows and columns.
Delete Hidden Rows and Columns in Excel (Quick and Easy)
How to delete hidden rows & columns in Excel?
Here are some easy and quick steps to delete all hidden rows or columns in Excel. 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.
How to find hidden rows in Excel?
One common way to identify hidden rows is by using the ‘Go to Special’ function in Excel. To do this, you can press Ctrl + G to open the ‘Go to’ dialog box, then click on the ‘Special’ button. From there, select ‘Visible cells only’ and click ‘OK’. This action will highlight only the visible cells, making it easier to spot hidden rows.
How to delete rows in Excel?
Go to Sort & Filter. From the drop-down select the option Filter. We can see the filtering drop-downs in the title bars of the dataset. Go to the drop-down option in the ‘Temporary’ column. Here we will deselect option 0. Click OK. So, we get the rows that we have to delete.
Can you delete hidden rows in 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.
