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

creating autofilter and using them thanks VBA
 

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" wrote in message
...
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

creating autofilter and using them thanks VBA
 

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(lngR ows)



'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

creating autofilter and using them thanks VBA
 

Using Ord as you do will not work.

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

"gocush" wrote in message
...
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(lngR ows)



'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