I have an advanced filter range of: $a$20:$P$352 and use the following to
query the results:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
Dim l As Long
If Target.Count 1 Then Exit Sub
If Target.Address = "$C$16" Then
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
r = Cells(Rows.Count, 1).End(xlUp).Row
If Target.Value = Sheets("Lists").Range("$A$1").Value Then
Sheets("Lists").Range("$C$2").Value = ""
Else
Sheets("Lists").Range("$C$2").Value = Target.Value
End If
Range("$a20:$P" & r).AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Sheets("Lists").Range("$C$1:$C$2"), Unique:=False
End If
If Target.Address = "$I$16" Then
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
l = Cells(Rows.Count, 1).End(xlUp).Row
If Target.Value = Sheets("Lists").Range("$D$1").Value Then
Sheets("Lists").Range("$E$2").Value = ""
Else
Sheets("Lists").Range("$E$2").Value = Target.Value
End If
Range("$a20:$P" & l).AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Sheets("Lists").Range("$f$1:$f$2"), Unique:=False
End If
End Sub
The query is based on a column of dates by day and I'm querying to display
based on the month I choose. January won't display..instead goes to line 353
and won't let me scroll up....
All the other months work swell.
Any advice? Thanks, Greg