collapsing columns base on criteria

  • Thread starter Thread starter Cesar Zapata
  • Start date Start date
C

Cesar Zapata

Hi,

and thanks in advance even if you are just reading this post. :)

I have few columns that I would like to collapse and Uncollapse base on
criteria. kind like a filter but horizontally.



this is how the data is set up


NYC UNITS NC UNITS FLA UNITS CA UNITS NYC SALES NC SALES FLA SALES CA
SALES NYC MARGIN NC MARGIN FLA MARGIN CA MARGIN
215 11395 603935 32 1696 89912 55 5 1321 7 3 1
3321 176013 9328689 494 26204 1388827 66 161 11 1 5 3
23 1219 64607 3 181 9618 56 1 1 7 2
131 6943 367979 19 1033 54783 11 11 4 2 1
38516 2041348 108191444 5734 303909 16107217 651 1 2 1 6 3
6546 346938 18387714 974 51651 2737507 1 2 1 6



I would like to have a button or outliner so the user can choose the state
and all the columns starting with that state will group. the other can hide.
so if I choose NYC then I'll get

NYC units--- NYC Sales ----- NYC margin.


is there any ideas how can be acomplish?

Thanks,
 
Try selecting your columns you want to hide and then play around with Data|Group
And Outline.

Then you can click on the giant -/+ symbols to hide/show detail.
 
Cesar,

Create a special sheet and list your states in column A (CA, FLA, NC, NYC)
Insert the name "Sts" refers to:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$N),1)
[this is a self-expanding name and allows adding other states, cities)

Create a userform with a combobox and a command button. Set the RowSource
of the combobox to Sts
Use this code for the button:

Private Sub CommandButton1_Click()
UserForm1.Hide
End Sub

Than add this sub in a standard module

Sub MyState()
Dim lcol As Long, x As Long, st As String

Application.ScreenUpdating = False
' prevents screen flicker

UserForm1.Show
st = UserForm1.ComboBox1.Value

lcol = ActiveSheet.Range("IV1").End(xlToLeft).Column
' finds last column on sheet

Range(Columns(1), Columns(lcol)).Hidden = True
' hides all columns

For x = 1 To lcol ' unhides specific columns
If InStr(1, Cells(1, x), st, 1) > 0 Then
Columns(x).EntireColumn.AutoFit
End If
Next

Application.Goto Reference:=Range("A1"), Scroll:=True
' scrolls to left side of worksheet

Application.ScreenUpdating = True
' resets screen updating
End Sub

Now add a button on your sheet or toolbar and assign the
macro MyState to this button.

hope this is what you are looking for...

steve
 
After looking at Steve's macro solution, another option is to set up custom
views with columns hiddent the way you want.

You can define/show these custom views by using:
View|custom view.
 
Back
Top