How To Delete Hidden Columns In Excel?

PuspaJanuary 31, 2023Last Updated: January 31, 2023

220

In Microsoft Excel, you may have hidden columns for various reasons, such as to simplify a large data set or to hide sensitive information. However, if you no longer need these hidden columns, you can easily delete them. This guide will show you the steps to remove hidden columns in Excel.

Table of Contents

How To Delete Hidden Columns In Excel?

To delete hidden columns in Excel, follow these steps:

Select the columns on either side of the hidden column(s).
Right-click and select “Unhide.”
The hidden columns will become visible, and you can then delete them as you would normally.

Alternatively, you can also use the following steps to delete hidden columns:

Select the entire worksheet by clicking the triangle at the top left corner of the worksheet.
Go to the “Home” tab.
Click “Find & Select” in the “Editing” section.
Select “Go To Special.”
In the “Go To Special” dialog box, select “Columns.”
Check the “Hidden” box.
Click “OK.”
The hidden columns will be selected.
Right-click and select “Delete.”

Note: This process may vary slightly depending on the version of Excel you are using.

Delete Hidden Rows and Columns using VBA

To delete hidden rows and columns in Excel using VBA, you can use the following code:

Sub DeleteHiddenRowsAndColumns()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") 'Change "Sheet1" to the name of your worksheet
    
    ws.Columns.SpecialCells(xlCellTypeVisible).EntireColumn.Delete
    ws.Rows.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End Sub

Open the Visual Basic Editor in Excel (press ALT + F11).
Insert a new module by selecting “Insert” from the top menu and then “Module.”
Copy and paste the code into the new module.
Replace “Sheet1” with the name of the worksheet you want to delete hidden rows and columns from.
Run the code by clicking “Run” or by pressing F5.

This code will delete all hidden rows and columns in the specified worksheet.

From an Entire Worksheet (Used Range)

To delete all hidden rows and columns in an entire worksheet (used range), you can use the following VBA code:

Sub DeleteHiddenRowsAndColumns()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") 'Change "Sheet1" to the name of your worksheet
    
    With ws
        .Cells.EntireColumn.Hidden = False
        .Cells.EntireRow.Hidden = False
        .UsedRange.SpecialCells(xlCellTypeVisible).EntireColumn.Delete
        .UsedRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
End Sub

Open the Visual Basic Editor in Excel (press ALT + F11).
Insert a new module by selecting “Insert” from the top menu and then “Module.”
Copy and paste the code into the new module.
Replace “Sheet1” with the name of the worksheet you want to delete hidden rows and columns from.
Run the code by clicking “Run” or by pressing F5.

This code will delete all hidden rows and columns in the entire used range of the specified worksheet.

From a Specific Range of Cells

To delete all hidden rows and columns within a specific range of cells in Excel, you can use the following VBA code:

Sub DeleteHiddenRowsAndColumns()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") 'Change "Sheet1" to the name of your worksheet
    Dim rng As Range
    Set rng = ws.Range("A1:D10") 'Change "A1:D10" to the range you want to delete hidden rows and columns from
    
    With rng
        .Columns.Hidden = False
        .Rows.Hidden = False
        .SpecialCells(xlCellTypeVisible).EntireColumn.Delete
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
End Sub

Open the Visual Basic Editor in Excel (press ALT + F11).
Insert a new module by selecting “Insert” from the top menu and then “Module.”
Copy and paste the code into the new module.
Replace “Sheet1” with the name of the worksheet you want to delete hidden rows and columns from.
Replace “A1:D10” with the range of cells you want to delete hidden rows and columns from.
Run the code by clicking “Run” or by pressing F5.

This code will delete all hidden rows and columns within the specified range of cells in the specified worksheet.

In conclusion,

You can delete hidden rows and columns in Excel using the built-in “Delete” option, the “Go To” feature, or VBA code. If you need to delete hidden rows and columns from a specific range of cells, you can modify the VBA code accordingly. Remember to always backup your data before making changes to your Excel files.