How To Delete Hidden Rows In Excel

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

Deleting hidden rows is a perfect example. In the older versions of Excel, this wasn't fifty-fifty possible. Thankfully, Microsoft decided to add information technology to versions 2007 and newer. Nevertheless, the way it works is a mystery to many.

There are two ways of deleting subconscious rows in Excel. Allow'due south go over them.

Using the Audit Document Function

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

In Excel, deleting subconscious rows and columns is an easy job. Here'south what yous have to do:

1. Open the workbook you desire to use, and go to File.
2. Now, click on Info.
3. Next, click on the Check for Issues button, so select Audit Document.
4. Inside the Document Inspector dialog box, make certain that Hidden Rows and Columns is selected then click Inspect.
5. The Document Inspector will show you a written report which shows you whether there are any hidden rows and columns. If there are, go to Remove All, then click Abolish.

Deleting Subconscious Rows on Previous Versions of Excel

This feature can exist 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 Certificate Inspector is the same. If you're using Excel 2007, y'all can find this option by clicking on the Office button, then going to Prepare > Inspect Document.

There's non a difference to the feature itself, so this will make sure that in that location are no subconscious rows and columns regardless of your version of Excel.

Using VBA Lawmaking to Delete Hidden Rows

This is a very convenient method if you only need to remove subconscious rows and columns from i sheet instead of a whole workbook. Information technology might not exist equally capable equally the Inspect Certificate feature, but it's a very easy and fast way of deleting rows from a worksheet. Hither'due south how to do it:

1. Open the Excel file and press Alt + F11 to open up VBA Editor.
2. Now, go to Insert > Module.
3. When the module window pops up, paste the post-obit code in it:

Sub deletehidden()
For lp = 256 To i Footstep -i
If Columns(lp).EntireColumn.Subconscious = True Then Columns(lp).EntireColumn.Delete Else
Adjacent
For lp = 65536 To 1 Step -ane
If Rows(lp).EntireRow.Hidden = Truthful Then Rows(lp).EntireRow.Delete Else
Next
Cease Sub

4. And so, press F5 to run the code.

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 procedure to clear the entire workbook of them in no time.

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

Using a 3rd-Party Software

At that place's a variety of 3rd-party solutions that can assist y'all remove hidden rows and columns in an instant. They ordinarily serve as an extension to Excel, adding more options to the toolbar. Bated from this, they also offer additional features that might be difficult to find, such as:

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

They may bear witness very useful if you lot'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 utilise. Thanks to Certificate Inspector, there'due south no demand to worry well-nigh hidden information.

If you find coding fun, executing VBA code is a more user-friendly solution, which yous can practice even if you don't have whatever coding feel. All you have to exercise is to cutting and paste the right commands.

Lastly, y'all can choose a third-political party solution that will assist you get the about out of Excel. They can be a smashing upgrade to the software and make it much easier to use.

Source: https://www.alphr.com/delete-hidden-rows-excel/

Posted by: clarkcasits.blogspot.com

Share this post