How to Delete Hidden Columns in Excel (2 Simple Methods)

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel is undoubtedly one of the most essential and potent computer programs currently available. Using Excel’s capabilities and resources, we can do unlimited computations on any dataset. This post will discuss two fundamental Excel methods for eliminating hidden columns. Therefore, use these 2 methods to delete hidden columns in Excel.

As an illustration, the below data set has some columns of Product, Month, Total Sales, and Sales Rep. Here, assume the D column alias Total Sales column is hidden. First, we are going to hide this column. Then, we’ll delete the Total Sale column in Excel using these two methods. Also, I have yet to mention that I’ve been writing this article using Microsoft Excel 365. However, you can use any other edition that provides maximum convenience.

delete hidden columns in excel


1. Using the Inspect Document Feature in Excel to Delete Hidden Columns

The Inspect Document feature usually removes all the hidden rows and columns in an Excel worksheet. It’s important to know that we can’t bring back the columns we just deleted. So, if you want to keep the data for a while, you need to keep the backup. To complete the task to your delight, be sure to stick to the procedures that are given in the upcoming sections.

STEPS:

  • First, select the column.
  • Right-click and choose Hide.

Use Inspect Document Feature in Excel to Delete Hidden Columns

  • The D column is now hidden.

Use Inspect Document Feature in Excel to Delete Hidden Columns

  • Go to File.

  • Click Info, then Check for Issues.
  • Choose Inspect Document.

Use Inspect Document Feature in Excel to Delete Hidden Columns

  • A new Microsoft Excel window appears and click on Yes.

  • At this time, the Document Inspector window pops up.
  • Next, choose Inspect.

Use Inspect Document Feature in Excel to Delete Hidden Columns

  • Select Hidden Rows and Columns as well as Remove All.
  • Next, choose Close.

Use Inspect Document Feature in Excel to Delete Hidden Columns

  • It will provide the desired outcome, as seen below.

Read More: How to Delete Every Other Column in Excel


2. Deleting Hidden Columns Through Excel VBA

VBA is a sophisticated approach for removing hidden columns. We can accommodate two unique situations. One is eliminating columns using VBA from an individual worksheet. A second scenario is removing columns from a specific dataset range.


2.1 Delete from Specific Range

There are occasions when we are required to modify the font style of a specific range of data. The following section is going to be of great assistance to you in this particular circumstance. To complete the assignment quickly, kindly follow the instructions below.

STEPS:

  • First, select the column.
  • Like previously, right-click and choose Hide.
  • As a result, the D column has been hidden.

Delete from Specific Range

  • Navigate to Developer, then Visual Basic.

  • Choose Insert and click Module.

Delete from Specific Range

  • Then, put the following VBA code into the Module box.
Sub DeleteHiddenColumns()
Dim sheet As Worksheet
Dim rng As Range
Dim LastColumn As Integer
Dim ColumnCount As Integer
Set sheet = ActiveSheet
Set rng = Range("A:E")
ColumnCount = rng.Columns.Count
LastColumn = rng.Columns(rng.Columns.Count).Column
For i = LastColumn To LastColumn - ColumnCount Step -1
If Columns(i).Hidden = True Then Columns(i).EntireColumn.Delete
Next
End Sub
  • Press F5 or click Run.

  • It will provide the desired result, as shown below.

Delete from Specific Range

Read More: How to Delete Column in Excel Without Affecting Formula


2.2 Erase from Whole Worksheet

In the next section, we’ll be able to delete hidden columns of the entire worksheet. Unlike previously, this time, we will hide two columns that are not contiguous. Follow the following guidelines to delete the hidden columns from a whole worksheet in Excel.

STEPS:

  • Choose the C and columns.
  • Right-click and select Hide.
  • Both the C and E columns have disappeared.

Erase from Whole Worksheet

  • Click Developer and then Visual Basic.

Erase from Whole Worksheet

  • Select Insert and then click Module.

  • Now, insert the code below into the Module box.
Sub DeleteHiddenColumns()
Dim sheet As Worksheet
Dim LastColumn
Set sheet = ActiveSheet
LastColumn = sheet.UsedRange.Columns(sheet.UsedRange.Columns.Count).Column
For i = LastColumn To 1 Step -1
If Columns(i).Hidden = True Then Columns(i).EntireColumn.Delete
Next
End Sub
  • Hit F5 or select the Run button.

Erase from Whole Worksheet

  • The intended outcome will finally be achieved, as seen below.

Erase from Whole Worksheet


Download Practice Workbook


Conclusion

You can delete hidden columns in Excel using the procedures outlined above. There are more articles like this one on our website. Continue using them, and let us know if you have any alternative methods of completing the task or fresh ideas. Remember to submit questions, comments, or recommendations in the space below.


Related Articles


<< Go Back to Delete Columns | Columns in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Lutfor Rahman Shimanto
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has worked with the ExcelDemy project for over a year. He has written 50+ articles and provided solutions of 100+ comments for ExcelDemy. Currently, he works as an Excel & VBA Developer and provides support and solutions in the ExcelDemy Forum. He has solved 100+ ExcelDemy Forum problems. His work and learning interests are in developing various Excel & VBA and Desktop applications. Outside of work, he enjoys Chess... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo