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.
Expand|Select|Wrap|Line Numbers
- 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