How To Delete All Hidden Rows Or Columns In Excel ?

Working with huge datasets or intricate spreadsheets can occasionally lead to the discovery of hidden rows or columns that can impede your analysis or obstruct the data's visual clarity. To maintain the correctness and effectiveness of your Excel operation, it is essential to remove these hidden components. In this lesson, we'll look at a variety of techniques for finding and removing Excel's hidden rows and columns. We'll go over a variety of methods that work with Excel 2010, 2013, 2016, 2019, and Excel for Microsoft 365, among other versions of the programme. This lesson will give you the knowledge and instructions you need, regardless of your level of Excel proficiency, to successfully remove hidden rows or columns from your spreadsheets.

You will have a thorough understanding of the many approaches available to locate and remove hidden rows or columns in Excel by the end of this tutorial. Your ability to navigate and manage your data more effectively will enable you to keep a spreadsheet that is neat and well−organized. Let's get started and learn how to erase all hidden rows and columns in Excel step by step!

Delete All Hidden Rows Or Columns

Here, we will first create a VBA module and then run it to complete the task. So let us see a simple process to learn how you can delete all hidden rows or columns in Excel.

Step 1

Consider any Excel sheet that contains hidden rows and columns as similar to the below image.

First, right−click on the sheet name and select View Code to open the VBA application. Then, click on Insert, select Module, and copy the below code into the textbox as shown below.

Right click > View code > Insert > Module > Copy.

Code

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

Step 2

Then save the sheet as a macro−enabled template and click F5 to complete the task.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can delete all hidden rows or columns in Excel to highlight a particular set of data.