467,917 Members | 1,434 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,917 developers. It's quick & easy.

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

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
  1. Sub ApplyDateFilter()
  2.  
  3.     Dim Ws As Worksheet
  4.     Dim filterStart As Long, filterEnd As Long
  5.     Dim i As Integer, reply As Integer
  6.     filterStart = Range("B1").Value 'assume this is the start date
  7.     filterEnd = Range("B2").Value 'assume this is the end date
  8.  
  9.         If filterStart = 0 Or filterEnd = 0 Then
  10.             reply = MsgBox("Please enter both filter dates!", vbOK, "Filter Dates")
  11.         Else
  12.             Application.ScreenUpdating = False
  13.  
  14.                 For i = 2 To Sheets.Count - 1 'ignores the first and last worksheet
  15.                 Set Ws = Sheets(i)
  16.                 Ws.AutoFilterMode = False 'Remove any existing filters
  17.                 Ws.Range("A8:H200").AutoFilter field:=1, Criteria1:=">=" & filterStart, _
  18.                 Operator:=xlAnd, Criteria2:="<=" & filterEnd
  19.                 Ws.Activate
  20.                 Ws.Range("I1").Select
  21.                 Center_it 'Puts filtered totals in visible window
  22.                 Next i
  23.  
  24.             Sheet1.Select
  25.             Range("B1:B2").Interior.ColorIndex = 3
  26.             Application.ScreenUpdating = True
  27.  
  28.         End If
  29. End Sub
Thanks for any help.
Apr 5 '17 #1
  • viewed: 4889
Share:
2 Replies
128KB
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
  1.  Ws.Range("A8:H200").AutoFilter field:=1, Criteria1:=">=" & filterStart, _
  2.                 Operator:=xlAnd, Criteria2:="<=" & filterEnd
  3.  
  4.  Ws.Range( Ws.Range("A8"), Cells(Rows.Count, 8).End(xlUp)).AutoFilter 1,">=" & filterStart, _
  5.                 xlAnd, "<=" & filterEnd
  6.  
  7.  
Jan 8 '20 #2
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).
Mar 2 '20 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by shaqattack1992-newsgroups | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.