creating autofilter and using them thanks VBA

[Guest]

hi,
I would like to know how can i create autofilter using VBA ?
after that, i would like to ue these filters with some criteria, but once
again i don't know how to do it.

for example :
i have a sheet with 2000 records.
1st step : sort all records by sorting columns A and B in ascending order.
Like we do using Data\Sort in the excel menu.
2nd step : create autofilters on the first row for all columns. like we do
using Data\autofilters.
3rd step : filter all records, using filtering criteria for column A and
column B
4th step : get count of how many records are displayed thanks these filters.

thanks a lot,
maileen

 

[Norman Jones]

Hi Maileen,

Try adapting the code obtained from the macro recorder when doing this
manually.

See also Debra Dalgeish's Autofilter pages, particularly her Autofilter
Programming page at:

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

 

[Guest]

Here are a couple of lines from the code I use to accomplish just what you
are doing. You will have to adjust the range names:

Dim Ord as sting
Ord ="xlAscending" 'actually I assign it to a range

'SEARCH
Range("Database").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range _
("Criteria2"), CopyToRange:=Range("Extract2"), Unique:=False

' 'Redefine ExtractedRecords
lngRows = Range("Extract2").CurrentRegion.Rows.count - 1
If lngRows < 1 Then lngRows = 1
ActiveWorkbook.Names.Add Name:="ExtractedRecords", RefersTo:= _
Sheets("Sheet2").Range("ExtractRange").Resize(lngRows)



'SORT
Range("ExtractedRecords").Sort Key1:=Range(rSortField), Order1:=Ord, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom

 

[Tom Ogilvy]

Using Ord as you do will not work.

The literal string "xlascending" will mean nothing as the argument to Order.