Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using AdvancedFilter on range of data
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using AdvancedFilter on range of data | Excel Discussion (Misc queries) | |||
Advancedfilter | Excel Programming | |||
Advancedfilter copy in place doesn't use criteria range correctly | Excel Programming | |||
AdvancedFilter with VBA | Excel Programming | |||
AdvancedFilter in VB | Excel Programming |