Auto Hide Columns

I have a spreadsheet like this:

-------------------------Dept 1----Dept 2----Dept 3----Dept 4----etc (Cells
C5:R5)
Expense Code A
Expense Code B
Expense Code C
Expense Code D
Expense Code E
etc

Drop down validation box in Cell B4.

When I choose a Dept from the dropdown box I want every other Dept Column to
be hidden.

My knowledge of VB doesn't extend beyond being able to paste into a module &
some basic editing so any help is appreciated on this !

Phil

Hi Phil

i can give you a solution to your situation, but i would rather like to
suggest a re-working of your spreadsheet to provide maximum flexibility.

if your data was in the format of
Department..............Expense.............Amount............Date

you could put a drop down box on columns A & B so that the user could easily
choose the department / expense to enter info for (Without having to scroll
left & right) and then you could easily generate a pivot table to give you
the format that you currently have and then you could easily show info for 1
department at a time without going anywhere near code.

if you're interested in this approach i'ld be happy to help you achieve it
.... alternatively if you want to press ahead with your current approach the
code you'll need to implement is

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$4" And Target.Value <> "" Then
For Each c In Range("C5:R5")
If c.Value <> Target.Value Then
c.EntireColumn.Hidden = True
End If
Next
ElseIf Target.Address = "$B$4" And Target.Value = "" Then
Columns("C:R").Select
Selection.EntireColumn.Hidden = False
Range("A4").Select
End If
End Sub

-- to implement the code, right mouse click on the sheet tab of the sheet
containing your information choose view code and paste the code directly
into the white page that comes up

Cheers
JulieD



"Phil Osman" <Phil [email protected]> wrote in message
news:[email protected]...
>I have a spreadsheet like this:
>
> -------------------------Dept 1----Dept 2----Dept 3----Dept 4----etc
> (Cells
> C5:R5)
> Expense Code A
> Expense Code B
> Expense Code C
> Expense Code D
> Expense Code E
> etc
>
> Drop down validation box in Cell B4.
>
> When I choose a Dept from the dropdown box I want every other Dept Column
> to
> be hidden.
>
> My knowledge of VB doesn't extend beyond being able to paste into a module
> &
> some basic editing so any help is appreciated on this !
>
> Phil

oh, i should have said that in the code provided - all columns will display
automatically again when B4 is blank

"JulieD" <[email protected]> wrote in message
news:[email protected]...
> Hi Phil
>
> i can give you a solution to your situation, but i would rather like to
> suggest a re-working of your spreadsheet to provide maximum flexibility.
>
> if your data was in the format of
> Department..............Expense.............Amount............Date
>
> you could put a drop down box on columns A & B so that the user could
> easily choose the department / expense to enter info for (Without having
> to scroll left & right) and then you could easily generate a pivot table
> to give you the format that you currently have and then you could easily
> show info for 1 department at a time without going anywhere near code.
>
> if you're interested in this approach i'ld be happy to help you achieve it
> ... alternatively if you want to press ahead with your current approach
> the code you'll need to implement is
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Address = "$B$4" And Target.Value <> "" Then
> For Each c In Range("C5:R5")
> If c.Value <> Target.Value Then
> c.EntireColumn.Hidden = True
> End If
> Next
> ElseIf Target.Address = "$B$4" And Target.Value = "" Then
> Columns("C:R").Select
> Selection.EntireColumn.Hidden = False
> Range("A4").Select
> End If
> End Sub
>
> -- to implement the code, right mouse click on the sheet tab of the sheet
> containing your information choose view code and paste the code directly
> into the white page that comes up
>
> Cheers
> JulieD
>
>
>
> "Phil Osman" <Phil [email protected]> wrote in message
> news:[email protected]...
>>I have a spreadsheet like this:
>>
>> -------------------------Dept 1----Dept 2----Dept 3----Dept 4----etc
>> (Cells
>> C5:R5)
>> Expense Code A
>> Expense Code B
>> Expense Code C
>> Expense Code D
>> Expense Code E
>> etc
>>
>> Drop down validation box in Cell B4.
>>
>> When I choose a Dept from the dropdown box I want every other Dept Column
>> to
>> be hidden.
>>
>> My knowledge of VB doesn't extend beyond being able to paste into a
>> module &
>> some basic editing so any help is appreciated on this !
>>
>> Phil
>
>

Hi Phil-

Just another thought based on Julie's suggestion about arranging the data
with each department as a separate row (record). You could then simply use
the Data>Filter>AutoFilter feature which would inherently provide for
selecting any given department from a list and the other rows would
"collapse".

Much less work than dealing with code and the feature can be turned on/off
as necessary.

HTH |:>)

"Phil Osman" wrote:

> I have a spreadsheet like this:
>
> -------------------------Dept 1----Dept 2----Dept 3----Dept 4----etc (Cells
> C5:R5)
> Expense Code A
> Expense Code B
> Expense Code C
> Expense Code D
> Expense Code E
> etc
>
> Drop down validation box in Cell B4.
>
> When I choose a Dept from the dropdown box I want every other Dept Column to
> be hidden.
>
> My knowledge of VB doesn't extend beyond being able to paste into a module &
> some basic editing so any help is appreciated on this !
>
> Phil

Thanks for your answer Julie, that piece of code works nicely.

I take your point about the layout of the spreadsheet, I would have favoured
a Pivot Table myself, but I have just been brought in as a contractor so the
workbook is already setup and they don't want to change the format !

Thanks again.
Phil

"JulieD" wrote:

> Hi Phil
>
> i can give you a solution to your situation, but i would rather like to
> suggest a re-working of your spreadsheet to provide maximum flexibility.
>
> if your data was in the format of
> Department..............Expense.............Amount............Date
>
> you could put a drop down box on columns A & B so that the user could easily
> choose the department / expense to enter info for (Without having to scroll
> left & right) and then you could easily generate a pivot table to give you
> the format that you currently have and then you could easily show info for 1
> department at a time without going anywhere near code.
>
> if you're interested in this approach i'ld be happy to help you achieve it
> .... alternatively if you want to press ahead with your current approach the
> code you'll need to implement is
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Address = "$B$4" And Target.Value <> "" Then
> For Each c In Range("C5:R5")
> If c.Value <> Target.Value Then
> c.EntireColumn.Hidden = True
> End If
> Next
> ElseIf Target.Address = "$B$4" And Target.Value = "" Then
> Columns("C:R").Select
> Selection.EntireColumn.Hidden = False
> Range("A4").Select
> End If
> End Sub
>
> -- to implement the code, right mouse click on the sheet tab of the sheet
> containing your information choose view code and paste the code directly
> into the white page that comes up
>
> Cheers
> JulieD
>
>
>
> "Phil Osman" <Phil [email protected]> wrote in message
> news:[email protected]...
> >I have a spreadsheet like this:
> >
> > -------------------------Dept 1----Dept 2----Dept 3----Dept 4----etc
> > (Cells
> > C5:R5)
> > Expense Code A
> > Expense Code B
> > Expense Code C
> > Expense Code D
> > Expense Code E
> > etc
> >
> > Drop down validation box in Cell B4.
> >
> > When I choose a Dept from the dropdown box I want every other Dept Column
> > to
> > be hidden.
> >
> > My knowledge of VB doesn't extend beyond being able to paste into a module
> > &
> > some basic editing so any help is appreciated on this !
> >
> > Phil
>
>
>

Hi Phil

you're welcome and thanks for the feedback - i understand about being only a
contractor ...

Cheers
JulieD


"Phil Osman" <[email protected]> wrote in message
news:[email protected]...
> Thanks for your answer Julie, that piece of code works nicely.
>
> I take your point about the layout of the spreadsheet, I would have
> favoured
> a Pivot Table myself, but I have just been brought in as a contractor so
> the
> workbook is already setup and they don't want to change the format !
>
> Thanks again.
> Phil
>
> "JulieD" wrote:
>
>> Hi Phil
>>
>> i can give you a solution to your situation, but i would rather like to
>> suggest a re-working of your spreadsheet to provide maximum flexibility.
>>
>> if your data was in the format of
>> Department..............Expense.............Amount............Date
>>
>> you could put a drop down box on columns A & B so that the user could
>> easily
>> choose the department / expense to enter info for (Without having to
>> scroll
>> left & right) and then you could easily generate a pivot table to give
>> you
>> the format that you currently have and then you could easily show info
>> for 1
>> department at a time without going anywhere near code.
>>
>> if you're interested in this approach i'ld be happy to help you achieve
>> it
>> .... alternatively if you want to press ahead with your current approach
>> the
>> code you'll need to implement is
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> If Target.Address = "$B$4" And Target.Value <> "" Then
>> For Each c In Range("C5:R5")
>> If c.Value <> Target.Value Then
>> c.EntireColumn.Hidden = True
>> End If
>> Next
>> ElseIf Target.Address = "$B$4" And Target.Value = "" Then
>> Columns("C:R").Select
>> Selection.EntireColumn.Hidden = False
>> Range("A4").Select
>> End If
>> End Sub
>>
>> -- to implement the code, right mouse click on the sheet tab of the sheet
>> containing your information choose view code and paste the code directly
>> into the white page that comes up
>>
>> Cheers
>> JulieD
>>
>>
>>
>> "Phil Osman" <Phil [email protected]> wrote in message
>> news:[email protected]...
>> >I have a spreadsheet like this:
>> >
>> > -------------------------Dept 1----Dept 2----Dept 3----Dept 4----etc
>> > (Cells
>> > C5:R5)
>> > Expense Code A
>> > Expense Code B
>> > Expense Code C
>> > Expense Code D
>> > Expense Code E
>> > etc
>> >
>> > Drop down validation box in Cell B4.
>> >
>> > When I choose a Dept from the dropdown box I want every other Dept
>> > Column
>> > to
>> > be hidden.
>> >
>> > My knowledge of VB doesn't extend beyond being able to paste into a
>> > module
>> > &
>> > some basic editing so any help is appreciated on this !
>> >
>> > Phil
>>
>>
>>