Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I used the "hide" feature on 3 columns - now I cannot get them b. | New Users to Excel | |||
How do I automatically hide columns in a worksheet based on a cell value? | Excel Worksheet Functions | |||
Auto update entire columns / rows??? | Excel Worksheet Functions | |||
hide columns | Excel Worksheet Functions | |||
AUTO HIDE ROWS | Excel Worksheet Functions |