Greetings,
I have a routine that iterates through many worksheets and Autofilters (using criteria filterStart and filterEnd) a range of data (A8:H200) using (field:=1). The code works well for that. What I want to do is filter using field:=1 unless field:=1 is empty, then I want to filter by another field. I have no idea how to accomplish this.
- Sub ApplyDateFilter()
-
-
Dim Ws As Worksheet
-
Dim filterStart As Long, filterEnd As Long
-
Dim i As Integer, reply As Integer
-
filterStart = Range("B1").Value 'assume this is the start date
-
filterEnd = Range("B2").Value 'assume this is the end date
-
-
If filterStart = 0 Or filterEnd = 0 Then
-
reply = MsgBox("Please enter both filter dates!", vbOK, "Filter Dates")
-
Else
-
Application.ScreenUpdating = False
-
-
For i = 2 To Sheets.Count - 1 'ignores the first and last worksheet
-
Set Ws = Sheets(i)
-
Ws.AutoFilterMode = False 'Remove any existing filters
-
Ws.Range("A8:H200").AutoFilter field:=1, Criteria1:=">=" & filterStart, _
-
Operator:=xlAnd, Criteria2:="<=" & filterEnd
-
Ws.Activate
-
Ws.Range("I1").Select
-
Center_it 'Puts filtered totals in visible window
-
Next i
-
-
Sheet1.Select
-
Range("B1:B2").Interior.ColorIndex = 3
-
Application.ScreenUpdating = True
-
-
End If
-
End Sub
Thanks for any help.
