Logic test
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
|