New User to this this site. Great site, have found a lot of great information.
Have not been able to find how to do this:

I have a Worksheet that I want to limit the columns that display based on a value selected in a dropdown list. This sheet is a worksheet that data entry will be completed on. The worksheet currently only contains a header row.

The value in the dropdown list only contains two values: ValueA, ValueB
The dropdown list was created using data validation, Allow list, and typing in ValueA, ValueB.

Column B Contains the dropdown list of ValueA, ValueB
What I want to do is:
If ColumnB contains ValueA, then display columns: A through H and K
If ColumnB contains ValueB, then display columns: A through C, I and J

How can I accomplish this?
Thanks for the assistance as I am stumped. HRwilkers

Copy this code into the Sheet that is used (ALT+F11, then select the proper
sheet on the left pane, then copy the code on the right pane. Note that the
code can be more compact as we can hide/display contiguous columns in one
line of code, but I voluntarily made it extensive for maintenance purposes.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address < "$B$1" Then
Exit Sub
End If

Select Case Target.Value
Case "ValueA"
Columns("A").Hidden = False
Columns("B").Hidden = False
Columns("C").Hidden = False
Columns("D").Hidden = False
Columns("E").Hidden = False
Columns("F").Hidden = False
Columns("G").Hidden = False
Columns("H").Hidden = False
Columns("I").Hidden = True
Columns("J").Hidden = True
Columns("K").Hidden = False
Case "ValueB"
Columns("A").Hidden = False
Columns("B").Hidden = False
Columns("C").Hidden = False
Columns("D").Hidden = True
Columns("E").Hidden = True
Columns("F").Hidden = True
Columns("G").Hidden = True
Columns("H").Hidden = True
Columns("I").Hidden = False
Columns("J").Hidden = False
Columns("K").Hidden = True
End Select

End Sub

--
Message posted via http://www.officekb.com

This works, but only for one cell. I need it to work for the entire column from B2 down. My guess is I need to use a range and modify this line, but can not figure out what to modify it with:
If Target.Address < "$B$1" Then

How do I modify this to apply to all of column B from B2 down? Thanks. Hrwilkers

This works, but only for one cell. I need it to work for the entire
column from B2 down. My guess is I need to use a range and modify this
line, but can not figure out what to modify it with:
If Target.Address < "$B$1" Then

How do I modify this to apply to all of column B from B2 down? Thanks.

I'm not sure I understand what you mean by "apply to all of column B from B2
down". Do you mean you want a ValueA or ValueB setting in ANY cell of column
B (except B1) to trigger the hiding of the columns you indicated? If so,
what about when some of the cells in column B are set to one value and the
remaining cells are set to the other value.. which cell governs the hiding
operation? Can you explain what you are trying to do in a little more
detail?

Rick

Completed, Thanks for the Assistance. At first when I tried this I was getting a Run Time Error "13", I then added an If Error statement and that got rid of the error.

The code below will Display certain columns based on the value in column C. If you click on a blank cell, then it displays all columns. If you click on the header at the top, then it displays all columns, now for the code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B:$B" Then
Exit Sub
End If
On Error Resume Next
Select Case Target.Value
Case ""
Columns("A").Hidden = False
Columns("B").Hidden = False
Columns("C").Hidden = False
Columns("D").Hidden = False
Columns("E").Hidden = False
Columns("F").Hidden = False
Columns("G").Hidden = False
Columns("H").Hidden = False
Columns("I").Hidden = False
Columns("J").Hidden = False
Columns("K").Hidden = False
Case "(Header Value)"
Columns("A").Hidden = False
Columns("B").Hidden = False
Columns("C").Hidden = False
Columns("D").Hidden = False
Columns("E").Hidden = False
Columns("F").Hidden = False
Columns("G").Hidden = False
Columns("H").Hidden = False
Columns("I").Hidden = False
Columns("J").Hidden = False
Columns("K").Hidden = False
Case "(Value A)"
Columns("A").Hidden = False
Columns("B").Hidden = False
Columns("C").Hidden = False
Columns("D").Hidden = False
Columns("E").Hidden = False
Columns("F").Hidden = False
Columns("G").Hidden = False
Columns("H").Hidden = False
Columns("I").Hidden = True
Columns("J").Hidden = True
Columns("K").Hidden = False
Case "(Value B)"
Columns("A").Hidden = False
Columns("B").Hidden = False
Columns("C").Hidden = False
Columns("D").Hidden = True
Columns("E").Hidden = True
Columns("F").Hidden = True
Columns("G").Hidden = True
Columns("H").Hidden = True
Columns("I").Hidden = False
Columns("J").Hidden = False
Columns("K").Hidden = True
End Select

End Sub



Thanks to all who assisted. This was done in Excel Version 2002.

Sorry, It should read Column "B", NOT Column "C" as above. Jaosn