VBA: Hide columns based on 2 conditions

[djecris]

Hi all!

I have a limited knowledge of VBA and I'm learning by trial and error, but this time I'm really stuck.

I would like to hide columns based on 2 conditions.

example, conditions here are A1 and B1:
if A1 = 2022 and B1 = 2023, then we hide columns R to W
if A1 = 2023 and B1 = 2025, then we hide columns I to N and U to W
etc.

The macro would be triggered as soon as a value is entered in B1.

Could someone give a solution to my problem?

 

[Joe4]

Welcome to the Board!

Right-click on the sheet tab name at the bottom of the screen, select View Code, and paste this VBA code in the VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Only run if cell B1 is updated
    If Intersect(Range("B1"), Target) Is Nothing Then Exit Sub
   
'   First unhide all columns
    Columns("I:W").Hidden = False
   
'   Check values of A1 and B1
    If Range("A1") = 2022 And Range("B1") = 2023 Then
        Columns("R:W").Hidden = True
    End If
    If Range("A1") = 2023 And Range("B1") = 2025 Then
        Columns("I:N").Hidden = True
        Columns("U:W").Hidden = True
    End If
   
End Sub

It should do what you want automatically, as you manually update the value in cell B1.

 

[djecris]

Welcome to the Board!

wow what a quick and clear answer thank you so much

 

[Joe4]

You are welcome!
Glad I was able to help.

I added some documentation to the code to show you what each step is doing.
Let me know if you have any questions about it.

 

[djecris]

You are welcome!
Glad I was able to help.

I added some documentation to the code to show you what each step is doing.
Let me know if you have any questions about it.

Thank you!

Sorry to ask, I have 2 follow-up questions, hoping you can answer:

1) You have put the modification of B1 as a trigger for the macro with "If Intersect(Range("B1"), Target) Is Nothing Then Exit Sub"
Is there a simple way to also have A1 as a trigger so that if either A1 or B1 is modified, the macro starts? I've tried to directly add a similar line below with A1 and to include A1 directly in the line you've written but neither is working.

2) I don't know it that's something simple to do but:
How can I add a third condition (in addition to A1 and B1) but with as a text and not a number?
For example:
if A1 = 2022 and B1 = 2023 and C1 = "Marketing", then we hide columns R to W

 

[Joe4]

1. Change this line:

    If Intersect(Range("B1"), Target) Is Nothing Then Exit Sub

to this:

    If Intersect(Range("A1:B1"), Target) Is Nothing Then Exit Sub

then it will run on any change to A1 or B1.

2. If you notice in my original code, there are two distinct IF...THEN blocks, one for each of your sets of conditions.
You can add more blocks like that for you other checks.
Give it a try, and see how you do, and post back if you run into issues.

 

[djecris]

1. Change this line:

    If Intersect(Range("B1"), Target) Is Nothing Then Exit Sub

to this:

    If Intersect(Range("A1:B1"), Target) Is Nothing Then Exit Sub

then it will run on any change to A1 or B1.

2. If you notice in my original code, there are two distinct IF...THEN blocks, one for each of your sets of conditions.
You can add more blocks like that for you other checks.
Give it a try, and see how you do, and post back if you run into issues.

It's working thank you ! Sorry for the question, I was sure that with a text variable it would have complexified the macro, but it's working fine!