Note that you need to place this VBA code in a regular module in the Visual Basic Editor. And in the process, it deletes all the hidden rows and columns that it encounters. 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. This is just the combined code for both rows and columns and works the same way. 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.Īnd 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: Sub DeleteHiddenRowsColumns() This again works the same way, where instead of rows, we are checking for columns. If Columns(i).Hidden = True Then Columns(i).EntireColumn.Delete In case you want to delete all the hidden columns in the used range, use the VBA code below: Sub DeleteHiddenColumns() This loop checks for all the rows, and deletes any hidden row that it encounters in the process. And in case it’s not hidden, the code leaves that row as is and moves to the row above it. In case it’s hidden, that entire row is deleted.
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. The above VBA code first finds out the last row number in the used range and assigns that row number to the variable ‘LastRow’. If Rows(i).Hidden = True Then Rows(i).EntireRow.Delete 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.Ī better method would be to see what’s the used range and then only check that used range for any hidden rows and columns.īelow is the VBA code that would delete all the hidden rows in the used range: Sub DeleteHiddenRows() Let’s look at different scenarios where you can use VBA to delete these hidden rows and columns. If you only have a few hidden rows and columns, it’s possible to unhide these manually and then delete it.īut 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. In that case, you can use the VBA method covered next. This is a great method if you want to delete all the hidden columns and rows for the entire workbook.īut in case you only want to delete it in a specific sheet or in a specific range, then you cannot use this option. So make sure you have a backup copy of the original data (in case if you may need it in the future) Note: You cannot undo the changes made by the Document Inspector. 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).Īpart 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. 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 the workbook is not saved already, Excel would first force you to save it. Note that you need to save this workbook before running the Document Inspector option. The above steps would delete all the hidden rows and columns in the workbook. You will see that it shows the total number of hidden rows and columns that it has found in the workbook.
Remember that it is going to remove these hidden rows and columns from the entire workbook and not from the active sheet only.īelow are the steps to delete all the hidden rows and columns from the workbook 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. Delete Hidden Rows and Columns using VBAĭelete All Hidden Rows and Columns in Excel.
Delete All Hidden Rows and Columns in Excel.
0 kommentar(er)
