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