Hello,
I just spent a great deal of time getting a table to filter based on a list of criteria. However, once the filter is applied, I lose all ability to apply any manual sorting and/or filtering as needed.
The CriteriaRange points to a Named Range on a "List" sheet using the following formula… as recommended everywhere I looked on this topic.
I tried using each of the following - separately - to restore the filter drop downs to no avail:
I assume this is how the Advanced Filtering works? It's a one off that prevents any subsequent manual sorting and/or filtering?
So, I think my question is two-part:
Many of the books have more than one criteria in the Series - separated by semicolons - which means I need to filter using wildcards if using vba.
For example, if one of the Series in the SeriesRange list is the "Berenstain Bears", I would need to check all of the following:
I just spent a great deal of time getting a table to filter based on a list of criteria. However, once the filter is applied, I lose all ability to apply any manual sorting and/or filtering as needed.
VBA Code:
'Filter Active Series
Range("t_" & wsName & "[#All]").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=SeriesRange, Unique:=False
Excel Formula:
=SUBSTITUTE(CHOOSECOLS(FILTER(t_AuthorSeries,((t_AuthorSeries[Status]="active")*((t_AuthorSeries[Student]="JohnDoe")+(t_AuthorSeries[Student]="All")))),1),";","")
VBA Code:
'Reenable AutoFilter
Range("t_" & wsName).AutoFilter 'option 1 cleared filtered range
Range("t_" & wsName).ShowAutoFilterDropDown = True 'option 2 does nothingSo, I think my question is two-part:
- Am I going about this the wrong way where I lose subsequent manual sorting/filtering?
- I need to use a different approach?
Many of the books have more than one criteria in the Series - separated by semicolons - which means I need to filter using wildcards if using vba.
For example, if one of the Series in the SeriesRange list is the "Berenstain Bears", I would need to check all of the following:
- Berenstain Bears;
- Berenstain Bears; Bright & Early Book;
- Berenstain Bears; Bright & Early Book; Berenstain Bear Scouts;
- Berenstain Bears Big Chapter Books;
- Living Lights; Berenstain Bears;
- First Time Books; Berenstain Bears;
- I Can Read! (HarperCollins): Level 1; Berenstain Bears;
- Step into Reading: Step 3; Berenstain Bears;
VBA Code:
Sub Filter_ActiveSeries()
Application.ScreenUpdating = False
'Data Types/Declarations
Dim Student As String
Dim wsName As String: wsName = ActiveSheet.Name
'Verify Sheet Name
Select Case wsName
Case "Books" 'prompt for Student Name
'Data Types
Dim Arr As Variant, Titlebar As String, i As Long, YNAnswer As Integer
'Declarations
Arr = Range("t_Students[Name]").Value
Titlebar = "Choose Student..."
'Build Message Box
Dim NewLine As String, Message As Variant
For i = LBound(Arr, 1) To UBound(Arr, 1)
NewLine = i & vbTab & Arr(i, 1) & vbNewLine
Message = Message + NewLine
Next i
On Error GoTo ErrHandler
'Input Prompt
Dim Choice As Integer: Choice = InputBox(Message, Titlebar)
If Choice >= LBound(Arr, 1) And Choice <= UBound(Arr, 1) Then 'If Valid Entry
Student = Arr(Choice, 1)
End If
Case Else: Exit Sub
End Select
'Set Active Worksheet by Name
Dim ws As Worksheet: Set ws = Worksheets(wsName)
'Declarations
Dim SeriesRange As Range
Set SeriesRange = Range("l_Lists_" & Student & "_Series")
Set SeriesRange = SeriesRange.Offset(-1, 0).Resize(SeriesRange.Rows.Count + 1, SeriesRange.Columns.Count)
'Clear any active filters
If ws.FilterMode Then ws.ShowAllData
'Filter Active Series
Range("t_" & wsName & "[#All]").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=SeriesRange, Unique:=False
'Re-enable Filter Drop Downs
' Range("t_" & wsName).AutoFilter 'it's clearing the advanced filter
' Range("t_" & wsName).ShowAutoFilterDropDown = True 'no action
Application.ScreenUpdating = True
ErrHandler: 'Exit on error
End Sub
