PDA

View Full Version : Solved: Performing a Successive Filter



Opv
06-29-2010, 08:29 AM
I am attempting to enhance the following filter subroutine to allow me to perform multiple filters without having to manually reset the data to show all rows each time.


Sub filterEnglish()
'''''' FILTER ENGLISH DATA '''''
'filterEnglish:

Application.EnableEvents = False
Application.screenUpdating = False

Range("A" & headingRow, Range("A" & headingRow).End(xlDown)).Select
Selection.AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range(EnglishFilterRange), _
Unique:=False

''''''''''SELECT HOME RANGE''''''''''

Range(startRange).Select
With ActiveSheet
If Selection.EntireRow.Hidden Then
Do
ActiveCell.Offset(1, 0).Select
Loop Until Not Selection.EntireRow.Hidden
End If
End With

Application.EnableEvents = True
Application.screenUpdating = True

''''''''''CLOSE USER FORM''''''''''
Range(EnglishFilterTerm).Value = ""
Range(CreekFilterTerm).Value = ""
Unload Me

End Sub


I have a separate subroutine called showAllData() which turns off all filters and and displays all data rows. Consequently, I tried incorporating the following script into the above subroutine:


With Sheets("Dictionary")
If .AutoFilterMode Or .FilterMode Then
Call showAllData
Application.EnableEvents = True
Application.screenUpdating = True
Call filterEnglish
End If
End With


I was hoping that when I click on the userform button to perform a subsequent filter request, that this modification would recognize that the data is already in filter mode, expand the data and then perform the next filter request.

What this is doing is that it unfilters the data and then stops. Can someone suggest a change to achieve what I am trying to achieve?

Thanks,

Opv

Bob Phillips
06-29-2010, 08:57 AM
Extend the criteria to all that you want.

Opv
06-29-2010, 09:11 AM
Extend the criteria to all that you want.

Unfortunately, that information is rarely known when the initial filter request is submitted.

Bob Phillips
06-29-2010, 10:00 AM
If you are going to use advanced filter, then you will need to build a wizard to get the values and construct the criteria. Not simple, but doable.

Opv
06-29-2010, 10:21 AM
If you are going to use advanced filter, then you will need to build a wizard to get the values and construct the criteria. Not simple, but doable.

Thanks. I figured out my problem. I was attempting to modify the actual code that filters the data. I moved my modification to the CommandButton1 code (which calls the filter script) and it checks for filter mode before the filter script is ever called and shows all data before the new filter term is processed.

Thanks for your help,

Opv