creating autofilter and using them thanks VBA

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

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

---
Regards,
Norman



"Maileen" <[email protected]> wrote in message
news:[email protected]...
> 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

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


"Maileen" wrote:

> 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

Using Ord as you do will not work.

The literal string "xlascending" will mean nothing as the argument to Order.
--
Regards,
Tom Ogilvy

"gocush" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> "Maileen" wrote:
>
> > 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