collapsing columns base on criteria
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
"Cesar Zapata" wrote in message
...
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,
|