Filtering Records Help Please

[Chris Hankin]

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

 

[Debra Dalgleish]

You can record a macro as you apply an advanced filter to the list.
There are instructions in Excel's Help, and here:

http://www.contextures.com/xladvfilter01.html

 

[Tom Ogilvy]

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

 

[Chris Hankin]

Thanks Debra,

I'll check the web-link you sent,

Cheers,

Chris.

Live Long and Prosper

 

[Chris Hankin]

Thanks very much Tom,

Your VBA code worked very well - much appreciated.

Cheers,

Chris.

Live Long and Prosper