Re: Using AdvancedFilter on range of data

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I am trying to split a range of data into 2 sheets -- one for employees
with a valid tech number and one for employees with no valid tech
number (tech number = "0000" or ""). The tech number is a text field
as there may be leading zeros.

I used the following code to create the *** for employees with no
valid tech numbers, but I'm having problems determining the correct
criteria to create the *** for employees with a valid tech number.
Here's the code:

Private Sub Test_Filter_Click()
Set sh = Worksheets("Compiled Totals")
Set rng = sh.Range(sh.Cells(8, "A"), _
sh.Cells(sh.Rows.Count, "O").End(xlUp))

'Create Upload Data for Hourly Non Technician Employees
Sheets("Upload Data Hourly").Select
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Field Rep Time
***").Range("A41:A43"), _
CopyToRange:=Sheets("Upload Data Hourly").Range("A2"), _
Unique:=False
Sheets("Upload Data Hourly").Rows(2).Delete

End Sub

Criteria range:
Tech_No
0000
=

For the second ***, I tried using the following criteria range with
no luck:

=d2<>""
=d2<>"0000"

If I could somehow set a criteria for all rows beginning with row 9 for
which the value of column d > 0, it would work. Any idea how to do
that?

Mat N wrote:
Is there a particular reason you want to use code for this? The
Autofilter can also to 2 criteria in the way you need and therefore
seems like an easier way to get the result you need?

Regardless I think your questions will be answered if you record a
macro and use the advanced filter menu to get what you are looking for.

The criteria range will need to include three of rows, one for the
field name and two for each of the criteria you are interested in
"0000" and blank (may need to be a zero lengthed string). I believe
these cells can not be a formula. If you are only interested in a
criteria based on one field then the criteria range only needs to be
one column wide.

The data range should include the field names (or header row).

Hope this helps.

Connie wrote:
I have a *** in my spread*** which contains employee data. Each
row of data represents an employee, and there is a header line. I want
to use an advanced filter to determine the employees for whom the Tech
No (column 4) is either blank or equal to "0000". Data begins on row 9
as there are headers on the page. The Tech No is a text field as there
could be leading zeroes in the number. I am using the following code
to determine the range of data for which I want to set the criteria (D9
through end of records). How do I set an advanced filter to select the
rows in which column 4 is either blank or equal to "0000"? Once I have
the filter set, I will be copying all the rows meeting the criteria to
another *** in the workbook. Any help would be appreciated. Connie

Private Sub Filter_Employee_Data_Click()
Dim sh As Work***
Dim rng As Range

Set sh = Worksheets("Compiled Totals")
sh.Select
Set rng = sh.Range(sh.Cells(9, "D"), _
sh.Cells(sh.Rows.Count, "D").End(xlUp))
Sheets("Compiled Totals").Range(rng.Address).Select

End Sub

.

Quantcast