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 Simple Methods to Delete Hidden Columns in Excel.
Download Practice Workbook
You are welcome to obtain a free copy of the example workbook we utilized throughout the session, which is available below.
2 Simple 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 utilize any other edition that provides maximum convenience.
1. Use 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 DÂ column.
- Then, right-click and choose Hide.
- Consequently, the D column is now hidden.
- After that, go to File.
- Later, click Info, then Check for Issues.
- Now, choose Inspect Document.
- Due to this, a new Microsoft Excel window appears.
- And click OK.
- At this time, the Document Inspector window pops up.
- Next, choose Inspect.
- After that, select Hidden Rows and Columns as well as Remove All.
- Subsequently, choose Close.
- Eventually, it will provide the desired outcome, as seen below.
Read More: How to Delete Extra Columns in Excel (7 Methods)
Similar Readings
- [Solved!] Can’t Delete Extra Columns in Excel (3 Solutions)
- How to Delete Multiple Columns by Number Using VBA in Excel (4 Ways)
- Clear Contents in Excel Without Deleting Formulas (3 Ways)
- How to Delete Unused Columns in Excel(Easiest 5 Methods)
- Delete Blank Columns in Excel (3 Ways)
2. Delete Hidden Columns Through Excel VBA
VBA is a sophisticated approach for removing hidden columns. We can accommodate two unique situations. One is eliminating columns 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:
- Firstly, select the DÂ column.
- Like previously, right-click and choose Hide.
- As a result, the D column has been hidden.
- Secondly, navigate to Developer, then Visual Basic.
- Now, choose Insert.
- Later click Module.
- 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
- After that, press F5 or click Run.
- It will eventually provide the desired result, as shown below.
Read More: VBA to Delete Column in Excel (9 Criteria)
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 Column from a whole worksheet in Excel.
STEPS:
- Firstly, choose the C and EÂ columns.
- Like before, right-click and select Hide.
- Consequently, Both C and E column has been concealed.
- Second, click Developer and then Visual Basic.
- Later, 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
- After that, hit F5 or select the Run button.
- The intended outcome will finally be achieved, as seen below.
Read More: How to Delete Multiple Columns in Excel
Conclusion
You can now Delete Hidden Columns in Excel using the procedures outlined above. There are more articles like this one on the ExcelDemy 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
- How to Delete Empty Columns with Header in Excel (4 Easy Ways)
- Delete Multiple Columns in Excel with Condition
- How to Delete Columns in Excel That Go on Forever (6 Ways)
- Can’t Delete Blank Columns in Excel (3 Problems & Solutions)
- How to Delete Columns Based on Header Using VBA in Excel
- Macro to Delete Columns in Excel (10 Methods)
- How to Delete Infinite Columns in Excel (4 Methods)