Hide or Unhide Excel Columns Automatically Using VBA

When working in Excel, it’s very common to have data where some columns contain numbers, others contain text, and some are completely blank. In situations like this, you may want to focus only on a specific type of data and hide the rest. Doing this manually every time can be slow and frustrating.

In this tutorial, you’ll learn a simple way to hide or unhide columns using VBA. With the help of a dropdown and a small piece of VBA code, Excel can automatically hide columns based on cell value—whether the cell contains a number, text, or is blank. This hide unhide columns using VBA approach is easy to follow and works great even if you’re new to VBA.

What You Will Learn?

By the end of this tutorial, you will know how to:

Automatically show only: Number columns, Text columns, Blank columns
Restore all hidden columns with one option
Use Worksheet_Change event effectively
Write clean, beginner-friendly VBA macros

Prepare the Worksheet

Type 1-13 from cell A8 to M8
Keep next two cells blank (N8:O8)
Type A-K from cell P8:Z8

Cell AB8 should be the cell where we will type Number, Text or Blank. If we type Number then only numbers will show and rest value will be hidden, and if we type text, then text will show and if type blank then only blank cells will show.
Note: We will check cells from A8:Z8 only)

Inserting VBA module

Press ALT+F11 to open the VBA editor.
Now go to Insert-> Module

Macro 1: Show Only Number Columns

Type the code as shown below to show only the number columns

				
					Sub Number()
    For i = 1 To 26
        If VarType(Sheet1.Cells(8, i).Value) = vbDouble Then
            Sheet1.Cells(8, i).EntireColumn.Hidden = False
        Else
            Sheet1.Cells(8, i).EntireColumn.Hidden = True
        End If
    Next i
    
    Sheet1.Range("A8").Select
End Sub

Explanation:

For i = 1 To 26

….

Next i

For is used to loop through column A to Z

VarType(Sheet1.Cells(8, i).Value) = vbDouble

This code is used to check if the cell contain number, VarType is used to get the variable type of the cell value and vbDouble is used to check if the cell value is only numeric.

Note: we can use int() instead of vbDouble but int treat blank cell a number with value 0.

Sheet1.Cells(8, i).EntireColumn.Hidden = False

above line is used to unhide the column

Sheet1.Cells(8, i).EntireColumn.Hidden = True

above line is used to hide the column

Sheet1.Range(“A8”).Select

above line is used to select cell A8

Now Run this code, you will see only numbers are shown are rest got hidden.

Macro 2: Show Only Text Columns

Now below the Number Macro, Type the code as shown below to show only the number columns

				
					Sub Text()
    For i = 1 To 26
        If VarType(Sheet1.Cells(8, i).Value) = vbString Then
            Sheet1.Cells(8, i).EntireColumn.Hidden = False
        Else
            Sheet1.Cells(8, i).EntireColumn.Hidden = True
        End If
    Next i
    
    Sheet1.Range("A8").Select
End Sub

Explanation:
vbString is used to detect the text string within the cell.

Macro 3: Show Only Blank Columns

Below the Text macro type the code as show below to show the blank columns.

				
					Sub Blank()
    For i = 1 To 26
        If IsEmpty(Sheet1.Cells(8, i).Value) Then
            Sheet1.Cells(8, i).EntireColumn.Hidden = False
        Else
            Sheet1.Cells(8, i).EntireColumn.Hidden = True
        End If
    Next i
    
    Sheet1.Range("A8").Select
End Sub

Explanation:
IsEmpty() checks for empty cells

Macro 4: Unhide All Columns

Below the Blank Macro type the code as shown below to unhide all the columns.

				
					Sub Unhide()
    For i = 1 To 26
        Sheet1.Cells(8, i).EntireColumn.Hidden = False
    Next i
End Sub

Explanation:
Unhides all the columns instantly.

Pro Tips

Change row being checked
Replace:

				
					Cells(8, i)

with

				
					Cells(5, i)

Increase column range
To check A–AZ:

				
					For i = 1 To 52

Request Customized Template

Frequently Asked Questions (FAQ)

1. Does this work automatically?
Yes. The code runs instantly when AB8 changes.

2. Can I apply this to another sheet?
Yes. Replace Sheet1 with your sheet name.

3. Is this beginner-safe?
Absolutely. No advanced VBA concepts required.

4. How do I hide columns automatically in Excel using VBA?
You can use the Worksheet_Change event to trigger a macro that hides or unhides columns based on cell values.

5. Can I hide columns based on text or numbers?
Yes, VBA functions like VarType and IsEmpty allow you to detect numbers, text, or blank cells.

Conclusion

This Excel VBA approach makes it easy to control which columns are visible without doing everything manually. Once it’s set up, Excel automatically hides or shows columns based on the data type, saving you time and effort.

If you work with dashboards, reports, or large datasets, this method is a practical way to hide or unhide columns using VBA and keep your worksheet clean and focused. It’s simple, flexible, and a great example of how VBA can make everyday Excel tasks easier.