472,961 Members | 1,558 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,961 software developers and data experts.

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


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()
  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
  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
  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
  24.             Sheet1.Select
  25.             Range("B1:B2").Interior.ColorIndex = 3
  26.             Application.ScreenUpdating = True
  28.         End If
  29. End Sub
Thanks for any help.
Apr 5 '17 #1
2 5991
272 256MB
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
  4.  Ws.Range( Ws.Range("A8"), Cells(Rows.Count, 8).End(xlUp)).AutoFilter 1,">=" & filterStart, _
  5.                 xlAnd, "<=" & filterEnd
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
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

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

Similar topics

by: RichieTJ | last post by:
-- Access Rookie Question-- Ok here is the situation: I have a form called frmServices, that has three fields called SvcNo, SvcName and SvcCity. I created a button that brings a popup form...
by: Randy | last post by:
I have trying to figure out the above topic. Can someone help me with this? I need for staff to be able to go to a dialog box or a form and then be able to enter data in mutliple fields and...
by: shaqattack1992-newsgroups | last post by:
Hello Everyone, At work, employees have been using a spreadsheet that I am trying to import into an access database to make some reports. The problem I'm having is that in the spreadsheet, they...
by: AtCor | last post by:
I am trying to filter data using count. For a given day and patient, I would like to return all the valid tests. When I use a count this way, it only returns the patients with at least 4 tests. I...
by: nehashri | last post by:
hi i am designing a database using Ms Access and ASP. i have 3 tables in access namely 'PERSONAL', other as 'POLICY' and 3rd one is named as 'STAFF'. in the contact table i have ID, Name, Children...
by: ahd2008 | last post by:
Hi everyone, I have a form for departments and personnel. There are there fields for the sake of search: DepartmentSearch as combo box Division\UnitSearch as combo box Number as combo box ...
by: tasawer | last post by:
Hi, I have an accident claims form that I need to requery on one of three different fields. Primary field is AccidentID ClientName (Can appear on more than one AccidentID) SolicitorsRef...
by: Andre Saputra | last post by:
Dear all. I really need help for my code. im using text box to filter data in listview, heres the code reloadlistview1 "select * from sales where TANGGAL between #" & Text1.Text & "#" & _ ...
by: mesairv | last post by:
Hi everyone. Im new here. I had been reading several topics in line with filtering, but i cannot find one related to my concern. Please help me to create a command button that will filter my...
by: DesignsOnline | last post by:
I have a table with many different fields in, including 40 separate image fields. ("Pic1" "Pic2" "Pic3" etc...) each field has a single value (an image name) in, I need to be able to select all...
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.