Hiding and Unhiding Columns Based on Multiple Cell Reference (Values)

[SONART]

Hi All,

I don't have much knowledge in VBA and in urgent need of help with a code to perform the following:

As shown below, I have a worksheet with a list of 6 criteria in A1:A6, with a data validation list in B1:B6 of "Yes" and "No" values.

In the worksheet I want columns C:N to be always hidden until I start selecting "Yes" from the data validation list in column B (see below). I allocated the following columns to each criteria:

Low = columns CD
Medium = columns EF
High = columns GH
Strong = columns IJ
Weak = columns KL
Other = columns MN

Based on my selection I want to only unhide the relevant columns for each criteira with a "Yes" value.

If I select Yes in criteria A1 (Low) and A3 (High) (please see Example 1 below), it will only unhide columns C:D and G:H.

Is it possible to make this dynamic, if I then change my selection it will then hide columns C:N and unhide the columns based on my new criteria selections with a "Yes" value in criteria A2 (Medium) and A5 (Weak) (please see Example 2 below), only unhiding columns E:F and K:L

I hope this makes sense. Any help on this problem would be much appreciated.

SONART


Example 1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

Low

Yes

2

Medium

No

3

High

Yes

4

Strong

No

5

Weak

No

6

Other

No

<TBODY>
</TBODY>

Example 2

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

Low

No

2

Medium

Yes

3

High

No

4

Strong

No

5

Weak

Yes

6

Other

No

<TBODY>
</TBODY>

 

[SONART]

Hi All,

Is there anyone who would be able to help me or provide some guidance with my query?

Thanks,
SONART

 

[storm8]

something like this.
you must place the code not in module but in the worksheet object

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rng As Range 'range that affect the selection
    
    Set rng = ActiveSheet.Cells(1, 2).Resize(6, 1) 'ie cells B1:B6
    
    Dim rx As Range
    Set rx = Application.Intersect(Target, rng)
    If rx Is Nothing Then Exit Sub
                
    Set rx = rx.Cells(1) 'make sure only 1 cell is evaluated
    
   'hides / show the colum which is + 2 of changed row. If you cange "B2", colum D will be affected; for B3 column E will be affected...
    If rx.Value = "yes" Then
        ActiveSheet.Columns(rx.Row+2).EntireColumn.Hidden = False
    ElseIf rx.Value = "no" Then
        ActiveSheet.Columns(rx.Row + 2).EntireColumn.Hidden = True
    End If
    
End Sub

 

[SONART]

Many thanks for your response storm8!

My model is now bigger than ben-hur. Is there any way you can hide and unhide a range of columns as shown below?

Low = columns U:AC
Medium = columns AD:AL
High = columns AM:AV
Strong = AR
Weak = columns AS
Other = columns AT:AV

SONART

 

[storm8]

here I defined a subs that does accept a range a hide it all

Sub hideCols(r As Range)
    Dim c As Range
    For Each c In r.Rows(1).Cells 'for each cells in 1st row of passed range
        c.EntireColumn.Hidden = True 'hide entire column of cell
    Next c
End Sub
Sub unhideCols(r As Range)
    Dim c As Range
    For Each c In r.Rows(1).Cells
        c.EntireColumn.Hidden = False
    Next c
End Sub

you can use it like this

Sub hiderandomstuff()
    hideCols Selection 'hide current selection
    hideCols Range("B:D")
    hideCols ActiveSheet.Cells(1, 5).Resize(1, 3) 'hides 3 columns from column "E"
    unhideCols ActiveSheet.Cells 'unhide all (takes long)
End Sub

 

[SONART]

Apologies formy ignorance storm8 but do I completely ignore your previous code and copy and paste the one above?
OR
Is the above an amendment from the first code you have provided?

 

[storm8]

you copy hide & unhide procedures below the original code, and then modify sub worksheet_change to call hide on appropriate ranges