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 wrote in message
...
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" wrote in message
...
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 wrote in message
...
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 DataFilterAutoFilter 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 wrote in message
...
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" wrote in message
...
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 wrote in message
...
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

Auto Hide Columns
 

Will this work if I just wanted to hide Rows instead?

"JulieD" wrote:

Hi Phil

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

Cheers
JulieD


"Phil Osman" wrote in message
...
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 wrote in message
...
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