Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2007 autofilter change to 2003 autofilter functionality? | Excel Discussion (Misc queries) | |||
2007 excel autofilter back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
2007 excel autofilter change back to 2003 autofilter? | Excel Discussion (Misc queries) | |||
2007 Autofilter worse than 2003 Autofilter | Excel Discussion (Misc queries) | |||
How to Sort within AutoFilter with Protection on (and AutoFilter . | Excel Discussion (Misc queries) |