About Us

Hrwilkers

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.

Quote:

Originally Posted by Rick Rothstein $MVP - VB$

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
auto fill columns based on a selection	ckane	Excel Discussion (Misc queries)	1	May 4th 07 05:34 AM
offer dropdown options based on another dropdown	Conor	Excel Discussion (Misc queries)	2	January 13th 06 04:28 PM
Dropdown list key selection	TrevorM	Excel Discussion (Misc queries)	1	October 3rd 05 07:57 PM
limit cell list selection based on the selection of another list	lorraine	Excel Worksheet Functions	2	December 14th 04 08:17 PM
Dropdown selection crashes	Kate Smith	Excel Worksheet Functions	1	November 19th 04 06:48 PM

Menu

About Us