I have an excel sheet. It is so setup that in Column A all states are
mentioned (they are Bold and shaded)...under each state there are some sub
categories...(could be different in each state)...In Column B to Column J
there is some data related to the sub categories....

Problem : It is a huge sheet and sometimes it becomes difficult to compare
data as whole of the data is not needed all the time.

Requirement : I want to get around it with some automation...that user may
be given some choices to pick the states and and columns it wants to see and
only the required data may be printed.

I don't want to name the ranges as sub categories/data often changes.....

I was thinking to populate the listbox with the Bold shaded heading in
Column A and give user the option to select the required only....similarly
giving users the option to select the required column....and hence the
unrequired is hidden.

But i am not aware how to accomplish it. Could anyone suggest me a different
Logic and/or wayout to do it. Suggestions will behighly appreciated.

--
Ajit

Ajit,

Here is one suggestion.

This code adds a code that replicates the state name, and adds a filter. You
can then filter by chosen state.

I have used a pattern of xlPatternHorizontal , you will need to see which
pattern you use from this list

xlPatternAutomatic
xlPatternChecker

xlPatternCrissCross

xlPatternDown

xlPatternGray16

xlPatternGray25

xlPatternGray50

xlPatternGray75

xlPatternGray8

xlPatternGrid
xlPatternHorizontal
xlPatternLightDown

xlPatternLightHorizontal

xlPatternLightUp

xlPatternLightVertical

xlPatternNone

xlPatternSemiGray75

xlPatternSolid

xlPatternUp

xlPatternVertical



Sub FilterIt()
Dim cRows As Long
Dim sState As String

cRows = Cells(Rows.Count, "A").End(xlUp).Row

Columns("B").Insert
sState = Cells(1, "A")
For i = 2 To cRows
With Cells(i, "A")
If .Interior.Pattern = xlPatternHorizontal Then
sState = .Value
Else
.Offset(0, 1).Value = sState
End If
End With
Next i
Columns("B:B").AutoFilter

End Sub


--

HTH

RP

"Ajit" wrote in message
...
I have an excel sheet. It is so setup that in Column A all states are
mentioned (they are Bold and shaded)...under each state there are some sub
categories...(could be different in each state)...In Column B to Column J
there is some data related to the sub categories....

Problem : It is a huge sheet and sometimes it becomes difficult to compare
data as whole of the data is not needed all the time.

Requirement : I want to get around it with some automation...that user may
be given some choices to pick the states and and columns it wants to see
and
only the required data may be printed.

I don't want to name the ranges as sub categories/data often changes.....

I was thinking to populate the listbox with the Bold shaded heading in
Column A and give user the option to select the required only....similarly
giving users the option to select the required column....and hence the
unrequired is hidden.

But i am not aware how to accomplish it. Could anyone suggest me a
different
Logic and/or wayout to do it. Suggestions will behighly appreciated.

--
Ajit