Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using AdvancedFilter on range of data
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 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using AdvancedFilter on range of data
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? 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
consolidation of tables in excel with text and figures | Excel Worksheet Functions | |||
ranking query | Excel Discussion (Misc queries) | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
data range props | Excel Discussion (Misc queries) |