Use Advanced Filter to Detect non blank rows
 

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 sheet for employees with no
valid tech numbers, but I'm having problems determining the correct
criteria to create the sheet 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
Sheet").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 sheet, 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?

Any help would be appreciated. Thanks!