Hide columns in an excel file based on cell value

djecris · Nov 25th 2021

Hi all,

I've been researching for an answer but didn't find one solving my problem yet.

Context:

- I'm building a file for team members to enter some data for their category (Sales, Marketing, HR, etc.) per quarter (Q1 2022, Q2 2022, etc)

- For now: all categories are displayed in columns: Sales from column C to column H, Marketing from column I to columns P, etc. for the other categories (so the file has many columns to fit all categories).

Objective:

- I would like to facilitate the usage of the file. Let's say if I write "Sales" in cell A1: I want only column C to H to appear, if I write "Marketing" in cell A1: I want only column I to P to appear, etc.

- Ideally, I would like the macro to run automatically when the text is entered in A1

I've tried to create a macro but it's wrong (the columns are hidden when I enter a value in A1, but when I change the text in A1, nothing happens)

Can someone help please?

Logit · Nov 25th 2021

This macro uses cell B2 for entering a number that activates hiding the columns.

Study the code and adapt for your use. Paste the entire macro in the Sheet Module :

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Target.Count > 1 Then Exit Sub
    Set rng = Target.Parent.Range("B2")
    If Intersect(Target, rng) Is Nothing Then Exit Sub

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    Cells.EntireColumn.Hidden = False
    Select Case rng
    Case 0
        Cells.EntireColumn.Hidden = False
    Case 1
        Range("C1,E1,G1,I1,K1").EntireColumn.Hidden = True
    Case 2
        Range("D1,F1,H1,J1,L1").EntireColumn.Hidden = True
    End Select

    Set rng = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Display More

djecris · Nov 26th 2021

Thank you for the answer but I cannot adapt it properly unfortunately

Anyone else to give me a quick solution? It's basically hiding/unhiding different columns based on a text in a cell

**royUK** · Nov 26th 2021

Why can't you adapt the code?

**royUK** · Nov 26th 2021

Try this

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.CountLarge > 1 And Target.Address <> "$A$1" Then Exit Sub
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        On Error GoTo reset
        Select Case Target.Value
            Case "Sales"
                Columns("I:P").EntireColumn.Hidden = False
                Columns("C:H").EntireColumn.Hidden = True
            Case "Marketing"
                Columns("C:H").EntireColumn.Hidden = False
                Columns("I:P").EntireColumn.Hidden = True
            Case Else: Columns("C:P").EntireColumn.Hidden = False
        End Select
reset:
        .EnableEvents = True
    End With
End Sub

Display More

A1 has a data validation list to select from.

djecris · Nov 26th 2021

Quote from royUK

Why can't you adapt the code?

I have tried this and it was not working, will check your proposition

Code

Private Sub Worksheet_Change(ByVal Target As String)
Dim rng As String
Set rng = Target.Parent.String("B2")
If Intersect(Target, rng) Is Nothing Then Exit Sub


With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Cells.EntireColumn.Hidden = False
Select Case rng
Case "Marketing"
Range("C1,E1,G1,I1,K1").EntireColumn.Hidden = True
Case "Sales"
Range("D1,F1,H1,J1,L1").EntireColumn.Hidden = True
End Select


Set rng = Nothing


With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Display More

djecris · Nov 26th 2021

Quote from royUK

Try this

I'm almost there thank you!!

Can you please tell me with the code below why when I write "Sales" or "Marketing", I have the left-hands columns that are not hidden ? (the columns on the right are hidden, but the ones on the left side with Supply from "E to I" are still visible and not hidden)

Code

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.CountLarge > 1 And Target.Address <> "$B$13" Then Exit Sub


With Application
.ScreenUpdating = False
.EnableEvents = False
On Error GoTo reset
Select Case Target.Value
Case "Supply"
Columns("E:I").EntireColumn.Hidden = False
Columns("J:BC").EntireColumn.Hidden = True
Case "Sales"
Columns("J:N").EntireColumn.Hidden = False
Columns("E:I,M:BC").EntireColumn.Hidden = True
Case "Marketing"
Columns("O:S").EntireColumn.Hidden = False
Columns("E:N,T:BC").EntireColumn.Hidden = True
Case Else: Columns("E:BC").EntireColumn.Hidden = False
End Select
reset:
.EnableEvents = True
End With
End Sub

Display More

djecris · Nov 29th 2021

Up,

does someone know what is wrong with my code below? I would really appreciate some help here,

Code

Sub Worksheet_Change(ByVal Target As Range)


If Target.CountLarge > 1 And Target.Address <> "$A$1" Then Exit Sub


With Application
.ScreenUpdating = False
.EnableEvents = False
On Error GoTo reset
Select Case Target.Value
Case "Sales"
Columns("I:P").EntireColumn.Hidden = False
Columns("C:H").EntireColumn.Hidden = True
Case "Marketing"
Columns("C:H").EntireColumn.Hidden = False
Columns("I:P").EntireColumn.Hidden = True
Case Else: Columns("C:P").EntireColumn.Hidden = False
End Select
reset:
.EnableEvents = True
End With
End Sub

Display More

**royUK** · Nov 29th 2021

That's actually my code!

Where have you used it?

Attach an example of your workbook containing the code.

djecris · Nov 29th 2021

Quote from royUK

That's actually my code!

Where have you used it?

Attach an example of your workbook containing the code.

I've attached the excel file in this message.

Sorry I've copied paste the wrong code. The code I wanted to post is the one below:

Code

Sub Worksheet_Change(ByVal Target As Range)


If Target.CountLarge > 1 And Target.Address <> "$B$1" Then Exit Sub


With Application
.ScreenUpdating = False
.EnableEvents = False
On Error GoTo reset
Select Case Target.Value
Case "Sales"
Columns("I:P").EntireColumn.Hidden = False
Columns("C:H").EntireColumn.Hidden = True
Case "Marketing"
Columns("C:H").EntireColumn.Hidden = False
Columns("I:P").EntireColumn.Hidden = True
Case Else: Columns("C:P").EntireColumn.Hidden = False
End Select
reset:
.EnableEvents = True
End With
End Sub

Display More

**royUK** · Nov 29th 2021

You have MergedCells that don't allow the columns to be hidden as you expect.

You should NEVER use MergedCells, they will invariably cause problems.

Why have you got multiple entries for 2021 Q4?

Also, why haven't you used the Data Validation List that I suggested?

djecris · Nov 29th 2021

Quote from royUK

You have MergedCells that don't allow the columns to be hidden as you expect.

You should NEVER use MergedCells, they will invariably cause problems.

Why have you got multiple entries for 2021 Q4?

Also, why haven't you used the Data Validation List that I suggested?

Display More

Oh ok thanks for that, I didn't know.

It's still not working though, do you know why when I type "Marketing", it doesn't show the desired columns? I guess there is something wrong :

Case "Marketing"

Columns("J:N").EntireColumn.Hidden = False

Columns("E:I,O:BC").EntireColumn.Hidden = True

full code:

Code

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.CountLarge > 1 And Target.Address <> "$B$1" Then Exit Sub


With Application
.ScreenUpdating = False
.EnableEvents = False
On Error GoTo reset
Select Case Target.Value
Case "Sales"
Columns("E:I").EntireColumn.Hidden = False
Columns("J:BC").EntireColumn.Hidden = True
Case "Marketing"
Columns("J:N").EntireColumn.Hidden = False
Columns("E:I,O:BC").EntireColumn.Hidden = True
Case "Supply"
Columns("O:S").EntireColumn.Hidden = False
Columns("E:N,T:BC").EntireColumn.Hidden = True
Case Else: Columns("E:BC").EntireColumn.Hidden = False
End Select
reset:
.EnableEvents = True
End With
End Sub

Display More

**royUK** · Nov 29th 2021

You need to remove the the merged cells and replace with Center Across Selection. I have done this for the first 3 departments.

Check the amended code

djecris · Nov 29th 2021

Quote from royUK

You need to remove the the merged cells and replace with Center Across Selection. I have done this for the first 3 departments.

Check the amended code

thank you so much! there is one last problem though: every time I modify a cell in excel: all columns appear back automatically, any idea why?

**royUK** · Nov 29th 2021

Try this amended code

djecris · Nov 29th 2021

Quote from royUK

Try this amended code

Sorry Roy, but I have the same problem: when I filter by Sales for ex and I enter a value: all columns appear

**royUK** · Nov 29th 2021

I didn't notice before but you have the code in Module 1 and the WorkBook event. I edited the code in Module 1 before

djecris · Nov 29th 2021

Quote from royUK

I didn't notice before but you have the code in Module 1 and the WorkBook event. I edited the code in Module 1 before

THANK YOU ! working perfectly

**royUK** · Nov 29th 2021

Pleased to help.

Post back if you need further help.

Visit my web site, http://www.excel-it.com, for more examples and some helpful articles.

djecris · Jan 3rd 2022

Quote from royUK

Pleased to help.

Post back if you need further help.

Visit my web site, http://www.excel-it.com, for more examples and some helpful articles.

Hi royUK and thank you again for your great help on the "hide columns" macro.

I have another difficulty and I have been trying to adapt the macro you gave me without success.

Instead of defining a target cell, I'd like to have 2 of them (it represents a range of years). Let me illustrate to make it clear:

if A1 = 2022 and C1 = 2023, then we hide columns R to W

if A1 = 2023 and C1 = 2025, then we hide columns I to N and U to W

etc.

I guess it requires an adaptation of the solution you gave me for my previous request but I cannot find the solution. Could you please help to put me on the right track?

Hide columns in an excel file based on cell value

Participate now!

Share