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. So let’s get started!
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: 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. 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.
From an Entire Worksheet (Used Range)
While I can make the VBA code check the entire worksheet starting from the last row number and the last column number in the worksheet, that would be a wastage of resources. A better method would be to see what’s the used range and then only check that used range for any hidden rows and columns. Below is the VBA code that would delete all the hidden rows in the used range: The above VBA code first finds out the last row number in the used range and assigns that row number to the variable ‘LastRow’. This last row number is then used in a For Next loop, where it starts from the last row and checks whether it’s hidden or not. In case it’s hidden, that entire row is deleted. And in case it’s not hidden, the code leaves that row as is and moves to the row above it. This loop checks for all the rows, and deletes any hidden row that it encounters in the process. In case you want to delete all the hidden columns in the used range, use the VBA code below: This again works the same way, where instead of rows, we are checking for columns. So it finds out the last column number in the used range, assigns it to a variable, and then uses the loop to go from the last column to the first column and delete all the hidden columns in the process. And in case you want to have a code that would delete all the hidden rows, as well as the hidden columns, use the VBA code below: This is just the combined code for both rows and columns and works the same way. Instead of one loop, this uses two separate loops, where it first goes through all the rows, and then it goes through all the columns. And in the process, it deletes all the hidden rows and columns that it encounters. Note that you need to place this VBA code in a regular module in the Visual Basic Editor. You can then run the code directly from the VB Editor, by using the macro dialog box, or by adding this macro to the quick access toolbar. If this is something that you need to do quite often, you can also add this code to the personal macro workbook so that you would have access to it from all the workbooks on your system.
From a Specific Range of Cells
In case you have a specific range from which you want to remove hidden rows and columns, you need to specify that within the code. This makes sure that the code only circles through the rows and columns in that specified range and leaves the other areas in the worksheet untouched Below the VBA code that would do this: In the above code, I have specified the range as A1:K200. This makes the code go through all the rows and columns in the specified range and remove any hidden rows and columns that it encounters. In case you have hidden rows or columns outside of this range, those would remain unaffected. So this is how you can delete hidden rows and columns in Excel. If you want to do it across the entire workbook, you can use the Document Inspector option. And in case you need more control, you can use the VBA codes as shown above. I hope you found this tutorial useful! Other Excel tutorials you may like:
How to Unhide Sheets in Excel (All In One Go) Delete Blank Columns in Excel How to Quickly Unhide COLUMNS in Excel How to Hide a Worksheet in Excel (that can not be unhidden) Hide Zero Values in Excel | Make Cells Blank If the Value is 0 How to Hide Formulas in Excel (and Only Display the Value) How to Select Entire Column (or Row) in Excel How to Group Columns in Excel?
