Auto Hide Columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
oh, i should have said that in the code provided - all columns will display
automatically again when B4 is blank
 
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 |:>)
 
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
 
Hi Phil

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

Cheers
JulieD
 
Back
Top