By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,730 Members | 1,559 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,730 IT Pros & Developers. It's quick & easy.

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

P: 7
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
Share this Question
Share on Google+
1 Reply


P: 51
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.  
1 Week Ago #2

Post your reply

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