Using AdvancedFilter on range of data

C

Connie

I have a sheet in my spreadsheet 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 sheet in the workbook. Any help would be appreciated. Connie

Private Sub Filter_Employee_Data_Click()
Dim sh As Worksheet
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

Sample Data:

EndDate EmployeeName OracleID TechNo TotalHours TrainingHours TechOffice VacationHours VacationEndDate BereavementHours JuryDutyHours ClericalHours WarehouseHours TotalHours PerDiem
10/7/2006 Michael
Albert 23709 1234 7.00 4.00 0.00 0.00 0.00 0.00 0.00 0.00 11.00 20
10/8/2006 Michael
Albert 23709 0000 5.85 0.00 4.10 0.00 0.00 0.00 0.00 0.00 9.95 0
10/9/2006 Michael
Albert 23709 0000 5.83 0.00 4.65 1.92 10/9/2006 0.00 0.00 0.00 0.00 12.40 0
10/10/2006 Michael
Albert 23709 0.00 6.42 4.10 0.00 1.92 0.00 0.00 0.00 12.43 0
10/11/2006 Michael
Albert 23709 0.00 0.00 10.42 0.00 0.00 1.92 0.00 0.00 12.33 0
10/12/2006 Michael
Albert 23709 1234 1.92 0.00 4.10 5.70 10/12/2006 0.00 0.00 0.00 0.00 11.72 0
10/13/2006 Michael
Albert 23709 1234 0.00 1.92 4.10 0.00 5.47 0.00 0.00 0.00 11.48 0
10/7/2006 Michael
Albert 23709 1234 7.00 4.00 0.00 0.00 0.00 0.00 0.00 0.00 11.00 20
10/8/2006 Michael
Albert 23709 1234 5.85 0.00 4.10 0.00 0.00 0.00 0.00 0.00 9.95 0
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top