Hiding columns based on cell contents

[Bill H]

I am looking for a solution to hide row or columns in a worksheet based upon
a value in a cell of another worksheet.

To be more specific, I have a "setup" worksheet, with a column if entires.
I have a second worksheet, "checklist" that I would like to format according
to the entries in "setup".

If setup:A1 is empty, I want to hide columns checklist 1-3
If setup:A2 is empty, I want to hide columns checklist 4-6.

ANy ideas how to set this up automatically ?

 

[Rick Rothstein]

Put this in the SetUp worksheet's code window...

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:A2")) Is Nothing Then Exit Sub
Worksheets("CheckList").Columns(1).Resize(, 3).Hidden = _
(Len(Range("A1").Value) = 0)
Worksheets("CheckList").Columns(4).Resize(, 3).Hidden = _
(Len(Range("A2").Value) = 0)
End Sub

 

[Bill H]

Rick,

I was not able to get this to work, so I tried a dumbed down version which
still didn't do anything:

Private Sub Worksheet_Change(ByVal Target As Range)

Worksheets("sheet4").Row(3).Hidden = True
Worksheets("sheet4").Row(4).Hidden = True
End Sub

Actually, this gave me a runtime error.

Any suggestions ?

Thanks
Bill H

 

[Rick Rothstein]

Your dumbed down version looks like you used different criteria than you
posted in your first message. You said your two sheets were named "SetUp"
and "CheckList", but your dumbed down version refers to Sheet4; and your
original message mentioned A1 and A2, but I'm not sure what Row(3) and
Row(4) are for (you were trying to hide columns, not rows). The code I
posted does work (I tested it before I posted it) provided the sheets are
named "SetUp" and "CheckList" (case is not important) and you are checking
the contents of A1 and A2 on the "SetUp" sheet and the code is placed in the
"SetUp" sheet's code window.