How do I filter data using one of two different fields?

Wade

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.

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

Thanks for any help.

SioSio

If you want to skip the empty line and process, it will be as follows. (By the way, named argument names can be omitted)

Expand|Select|Wrap|Line Numbers

 
 Ws.Range("A8:H200").AutoFilter field:=1, Criteria1:=">=" & filterStart, _

                Operator:=xlAnd, Criteria2:="<=" & filterEnd
 
 Ws.Range( Ws.Range("A8"), Cells(Rows.Count, 8).End(xlUp)).AutoFilter 1,">=" & filterStart, _

                xlAnd, "<=" & filterEnd

lewish95

Create a new parameter:

data type string
allowable values set to list
add from field - user field
Quick filter on user-sending

select condition tab, By formula
[user.sending]=[user.parameter]
Repeat for user-receiving sheet.

Lastly, right-click user.parameter and select 'add to sheet' (or add it to you dashboard).

How do I filter data using one of two different fields?

Post your reply

Similar topics