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

SONART

New Member
Joined
Aug 18, 2010
Messages
43
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
ABCDEFGHIJKLMN
1LowYes
2MediumNo
3HighYes
4StrongNo
5WeakNo
6OtherNo

<TBODY>
</TBODY>


Example 2
ABCDEFGHIJKLMN
1LowNo
2MediumYes
3HighNo
4StrongNo
5WeakYes
6OtherNo

<TBODY>
</TBODY>
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

SONART

New Member
Joined
Aug 18, 2010
Messages
43
Hi All,

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

Thanks,
SONART
 

storm8

Active Member
Joined
Apr 7, 2010
Messages
327
something like this.
you must place the code not in module but in the worksheet object

Code:
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

New Member
Joined
Aug 18, 2010
Messages
43
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

Active Member
Joined
Apr 7, 2010
Messages
327

ADVERTISEMENT

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

Code:
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

Code:
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

New Member
Joined
Aug 18, 2010
Messages
43
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

Active Member
Joined
Apr 7, 2010
Messages
327
you copy hide & unhide procedures below the original code, and then modify sub worksheet_change to call hide on appropriate ranges
 

Watch MrExcel Video

Forum statistics

Threads
1,127,163
Messages
5,623,103
Members
415,954
Latest member
TylerSeeds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top