How To Delete All Hidden Rows In Excel

Mastering Excel takes a lot of time and effort. Unless you’re an expert, getting a hang of the advanced features might be a daunting process. Unfortunately, not all commands are clearly visible within the interface.

How To Delete All Hidden Rows In Excel

Deleting hidden rows is a perfect example. In the older versions of Excel, this wasn’t even possible. Thankfully, Microsoft decided to add it to versions 2007 and newer. Still, the way it works is a mystery to many.

There are two ways of deleting hidden rows in Excel. Let’s go over them.

Using the Inspect Document Function

The Document Inspector feature is available in Excel, Word, PowerPoint, and Visio. It’s the best way to uncover and delete any hidden data that might be present in a document. This is useful when you have to share documents with other people and want to make sure there are no surprises in the document.

In Excel, deleting hidden rows and columns is an easy job. Here’s what you have to do:

  1. Open the workbook you want to use, and go to File. How To Delete All Hidden Rows In Excel
  2. Now, click on Info. How To Delete All Hidden Rows In Excel
  3. Next, click on the Check for Issues button, then select Inspect Document. How To Delete All Hidden Rows In Excel
  4. Within the Document Inspector dialog box, make sure that Hidden Rows and Columns is selected and then click Inspect.How To Delete All Hidden Rows In Excel
  5. The Document Inspector will show you a report which shows you whether there are any hidden rows and columns. If there are, go to Remove All, then click Cancel.

How To Delete All Hidden Rows In Excel

Deleting Hidden Rows on Previous Versions of Excel

This feature can be found in the same place in Excel 2013 and 2016. The interface of the 2010 version looks a bit different, but the pathway to the Document Inspector is the same. If you’re using Excel 2007, you can find this option by clicking on the Office button, then going to Prepare > Inspect Document.

How To Delete All Hidden Rows In Excel

There’s not a difference to the feature itself, so this will make sure that there are no hidden rows and columns regardless of your version of Excel.

Using VBA Code to Delete Hidden Rows

This is a very convenient method if you only need to remove hidden rows and columns from one sheet instead of a whole workbook. It might not be as capable as the Inspect Document feature, but it’s a very easy and fast way of deleting rows from a worksheet. Here’s how to do it:

  1. Open the Excel file and press Alt + F11 to open VBA Editor. How To Delete All Hidden Rows In Excel
  2. Now, go to Insert > Module. How To Delete All Hidden Rows In Excel
  3. When the module window pops up, paste the following code in it:

Sub deletehidden()
For lp = 256 To 1 Step -1
If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else
Next
For lp = 65536 To 1 Step -1
If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else
Next
End Sub

  1. Then, press F5 to run the code.

How To Delete All Hidden Rows In Excel

This will remove all hidden rows and columns from the sheet you’re working on. If there aren’t many of them, you can repeat this process to clear the entire workbook of them in no time.

The only issue that can occur is if there are any formulas in the hidden rows and columns. If they affect the data within a sheet, deleting them might cause some of the functions to not work properly, and you might end up with some incorrect calculations.

Using a Third-Party Software

There’s a variety of third-party solutions that can help you remove hidden rows and columns in an instant. They usually serve as an extension to Excel, adding more options to the toolbar. Aside from this, they also offer additional features that might be hard to find, such as:

  1. Batch delete checkboxes
  2. Batch delete option buttons
  3. Delete blank rows,
  4. Batch delete all macros

They may prove very useful if you’re a heavy Excel user and are looking for quick solutions to the common issues that Microsoft has yet to address properly.

The Final Word

The fact that Excel is equipped with the ability to discover and delete hidden rows and columns makes it much more convenient to use. Thanks to Document Inspector, there’s no need to worry about hidden information.

If you find coding fun, executing VBA code is a more convenient solution, which you can do even if you don’t have any coding experience. All you have to do is to cut and paste the right commands.

Lastly, you can choose a third-party solution that will help you get the most out of Excel. They can be a great upgrade to the software and make it much easier to use.

Sign up and earn $1000 a day ⋙

Driver Booster 12 Free: Keep 9.5M+ Drivers Up-to-Date in 1-Click

Driver Booster 12 Free: Keep 9.5M+ Drivers Up-to-Date in 1-Click

Driver Booster 12 Free is an effective tool that will keep your computers drivers up to date, which will make the system run faster and more reliably. This driver updater from IObit keeps your PC running at its best by checking for lost, out-of-date, or broken drivers immediately.

Advanced SystemCare 17 Free Review 2024 - Your Best PC Optimizer

Advanced SystemCare 17 Free Review 2024 - Your Best PC Optimizer

In an era where digital efficiency is paramount, Advanced SystemCare 17 Free emerges as a beacon for those seeking to enhance their PC's performance.

Summary of Movies & TV application shortcuts on Windows 10

Summary of Movies & TV application shortcuts on Windows 10

Summary of Movies & TV application shortcuts on Windows 10, Summary of Movies & TV application shortcuts on Windows 10 to bring you a great experience. Maybe

How to fix Messages Failed to Load error on Discord for Windows

How to fix Messages Failed to Load error on Discord for Windows

How to fix Messages Failed to Load error on Discord for Windows, Discord isn't fun if you can't read what other people write. Here's how to fix Messages error

How to display the This PC icon on the Windows 11 desktop

How to display the This PC icon on the Windows 11 desktop

How to display the This PC icon on the Windows 11 desktop, During the process of using Windows 11, many users need to access This PC (management).

How to find information in the Windows Registry quickly

How to find information in the Windows Registry quickly

How to find information in the Windows Registry quickly, Do you find it difficult to find information in the Windows Registry? So below are quick ways to find the registry

How to limit the number of failed login attempts on Windows 10

How to limit the number of failed login attempts on Windows 10

How to limit the number of failed login attempts on Windows 10. Limiting the number of failed password login attempts on Windows 10 helps increase computer security. Here's how

How to create fake error messages in Windows

How to create fake error messages in Windows

How to create fake error messages in Windows, Windows can come up with some pretty creative error messages but why don't you try creating your own content for them to make fun of?

Ways to open Windows Tools in Windows 11

Ways to open Windows Tools in Windows 11

Ways to open Windows Tools in Windows 11, Windows Administrative Tools or Windows Tools are still useful on Windows 11. Here's how to find Windows Tools in Windows 11.

How to fix Windows Quick Assist not working error

How to fix Windows Quick Assist not working error

How to fix Windows Quick Assist not working error, Windows Quick Assist helps you connect to a remote PC easily. However, sometimes it also generates errors. But,

How to pin Word, Excel and PowerPoint files to the corresponding app icon on the Windows 11 taskbar

How to pin Word, Excel and PowerPoint files to the corresponding app icon on the Windows 11 taskbar

How to pin Word, Excel and PowerPoint files to the corresponding app icon on the Windows 11 taskbar, How to pin Office files to the taskbar icon on Windows 11? Invite

How to fix the error of not being able to install software on Windows

How to fix the error of not being able to install software on Windows

How to fix the error of not being able to install software on Windows, Why can't you install apps or software on Windows 10/11? Here's everything you need to know about how to fix it

Instructions for deleting or changing PIN on Windows 11

Instructions for deleting or changing PIN on Windows 11

Instructions for deleting or changing the PIN code on Windows 11, In Windows 11, the PIN code is a very useful and convenient security tool for users. However some people

How to fix There Are Currently No Power Options Available error in Windows 10

How to fix There Are Currently No Power Options Available error in Windows 10

How to fix There Are Currently No Power Options Available error in Windows 10, Can't select power mode in Windows 10, what should I do? Here's how to fix the error

The simplest way to fix Photos application errors on Windows 10

The simplest way to fix Photos application errors on Windows 10

The simplest way to fix Photos app errors on Windows 10, what should I do if Microsoft Photos doesn't work? Don't worry about ways to fix Photos app errors on Windows

Instructions for installing keyboard shortcuts to switch input languages ​​on Windows 11

Instructions for installing keyboard shortcuts to switch input languages ​​on Windows 11

Instructions for installing shortcuts to switch input languages ​​on Windows 11. During the process of using Windows, users will often have to switch between methods.

How to check supported power status on Windows 11

How to check supported power status on Windows 11

How to check power status is supported on Windows 11, Windows 11 can handle many different power states. Here's how to check the power status

How to switch from 2.4GHz to 5GHz in Windows 10

How to switch from 2.4GHz to 5GHz in Windows 10

How to switch from 2.4GHz to 5GHz in Windows 10, If you want to find a quick and simple way to speed up the Internet, changing the WiFi band from 2.4GHz to 5GHz may help.

How to fix Not Enough Memory to Run Microsoft Excel error on Windows

How to fix Not Enough Memory to Run Microsoft Excel error on Windows

How to fix Not Enough Memory to Run Microsoft Excel error on Windows, Are you having an error of not enough memory to run Microsoft Excel? So, how to fix Not Enough Memory error

Ways to find all video files on Windows

Ways to find all video files on Windows

Ways to find all video files on Windows, Can't find recorded and saved videos on PC. Below are ways to help you find all clips on Windows.