Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering Records Help Please
Hello,
Could someone please help me with the following: In column A are a series of single digit numbers (0, 1, 2, 3, 4, 5, 6, 7 ,8 ,9). There is one single digit per row and approximately 21,000 rows. They are not in any particular order. The Header-row name located in cell A1 is named: 141_Stat_Code. I wish to filter column A to show only the single digit numbers: 0, 1, 2, 5, 7, 8. The worksheet name is: Unacquitted_142s I would like to run a macro to filter my desired records as detailed above. I am using Excel 2003. If anyone could please help that would be greatly appreciated. Thanks, Chris. Live Long and Prosper :) *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering Records Help Please
You can record a macro as you apply an advanced filter to the list.
There are instructions in Excel's Help, and he http://www.contextures.com/xladvfilter01.html Chris Hankin wrote: Hello, Could someone please help me with the following: In column A are a series of single digit numbers (0, 1, 2, 3, 4, 5, 6, 7 ,8 ,9). There is one single digit per row and approximately 21,000 rows. They are not in any particular order. The Header-row name located in cell A1 is named: 141_Stat_Code. I wish to filter column A to show only the single digit numbers: 0, 1, 2, 5, 7, 8. The worksheet name is: Unacquitted_142s I would like to run a macro to filter my desired records as detailed above. I am using Excel 2003. If anyone could please help that would be greatly appreciated. Thanks, Chris. Live Long and Prosper :) *** Sent via Developersdex http://www.developersdex.com *** -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering Records Help Please
Another approach is to add a column to the right
in F2 for example =if(or(A2<3,A2=5,A2=7,A2=8),"Show","No Show") then drag fill it down the column. Then apply an autofilter and filter on the column with the formula. You can also do this with a macro (uses the next available column) Sub ABCD() Dim rng As Range, s As String Dim rng1 As Range Set rng1 = Cells(1, "IV").End(xlToLeft)(1, 2) s = "=if(or(A2<3,A2=5,A2=7,A2=8),""Show"",""No Show"")" Set rng = Range(Cells(2, 1), Cells(2, 1).End(xlDown)) rng.Offset(0, rng1.Column - 1).Formula = s Cells(1, rng1.Column).Value = "Header6" Range("A1").CurrentRegion.AutoFilter Field:=rng1.Column, _ Criteria1:="Show" End Sub -- Regards, Tom Ogilvy "Debra Dalgleish" wrote in message ... You can record a macro as you apply an advanced filter to the list. There are instructions in Excel's Help, and he http://www.contextures.com/xladvfilter01.html Chris Hankin wrote: Hello, Could someone please help me with the following: In column A are a series of single digit numbers (0, 1, 2, 3, 4, 5, 6, 7 ,8 ,9). There is one single digit per row and approximately 21,000 rows. They are not in any particular order. The Header-row name located in cell A1 is named: 141_Stat_Code. I wish to filter column A to show only the single digit numbers: 0, 1, 2, 5, 7, 8. The worksheet name is: Unacquitted_142s I would like to run a macro to filter my desired records as detailed above. I am using Excel 2003. If anyone could please help that would be greatly appreciated. Thanks, Chris. Live Long and Prosper :) *** Sent via Developersdex http://www.developersdex.com *** -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering Records Help Please
Thanks Debra,
I'll check the web-link you sent, Cheers, Chris. Live Long and Prosper :) *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering Records Help Please
Thanks very much Tom,
Your VBA code worked very well - much appreciated. Cheers, Chris. Live Long and Prosper :) *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filtering a list with 67,000 plus records | Excel Discussion (Misc queries) | |||
Filtering records | New Users to Excel | |||
Filtering out old records. | Excel Discussion (Misc queries) | |||
Filtering Records | Excel Worksheet Functions | |||
Filtering records | Excel Programming |