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
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