Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
109 Views
