| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening the Autofilter dialog box
I have gotten frustrated with trying to get my code to work autofiltering
date ranges. The code I am using includes: Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="=firstdate", Operator:=xlAnd, Criteria2:="<=seconddate" The result is that the entire range is hidden. How can I get the default autofilter dialog to show? I have decided this is the best solution unless the code I am using can be altered successfully. Thanks, Jim -- Pops Jackson |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Opening the Autofilter dialog box
is this just illustrative or are firstdate and seconddate actually string
variables containing the date in the correct format. If the latter Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="=" & firstdate, _ Operator:=xlAnd, Criteria2:="<=" & seconddate -- Regards, Tom Ogilvy "Pops Jackson" wrote: I have gotten frustrated with trying to get my code to work autofiltering date ranges. The code I am using includes: Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="=firstdate", Operator:=xlAnd, Criteria2:="<=seconddate" The result is that the entire range is hidden. How can I get the default autofilter dialog to show? I have decided this is the best solution unless the code I am using can be altered successfully. Thanks, Jim -- Pops Jackson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Opening the Autofilter dialog box
I think the problem is in the use of variables - you have specified
literal text, so the filter is happening on the TEXT - try something like dim StartDate as string, secondDate as string StartDate="="& format(whateverdate,whateverformat) Selection.AutoFilter Field:=1, Criteria1:=StartDate etc Pops Jackson wrote: I have gotten frustrated with trying to get my code to work autofiltering date ranges. The code I am using includes: Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="=firstdate", Operator:=xlAnd, Criteria2:="<=seconddate" The result is that the entire range is hidden. How can I get the default autofilter dialog to show? I have decided this is the best solution unless the code I am using can be altered successfully. Thanks, Jim -- Pops Jackson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Opening the Autofilter dialog box
Tom, you always come through. This works perfectly. The first column
contains trade dates, with numerous trades on a particular date. The routine being developed will copy the selected range (in the date column and in specific other columns) and paste it all in another workbook which has formulae and macros for analysis of the data. I had everything else working fine for a specific date but was informed that a date range was required. Your suggested correction has helped me get it done. Thanks, Jim -- Pops Jackson "Tom Ogilvy" wrote: is this just illustrative or are firstdate and seconddate actually string variables containing the date in the correct format. If the latter Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="=" & firstdate, _ Operator:=xlAnd, Criteria2:="<=" & seconddate -- Regards, Tom Ogilvy "Pops Jackson" wrote: I have gotten frustrated with trying to get my code to work autofiltering date ranges. The code I am using includes: Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="=firstdate", Operator:=xlAnd, Criteria2:="<=seconddate" The result is that the entire range is hidden. How can I get the default autofilter dialog to show? I have decided this is the best solution unless the code I am using can be altered successfully. Thanks, Jim -- Pops Jackson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Opening the Autofilter dialog box
Pops jackson -
Great, glad it worked! -- Regards, Tom Ogilvy "Pops Jackson" wrote: Tom, you always come through. This works perfectly. The first column contains trade dates, with numerous trades on a particular date. The routine being developed will copy the selected range (in the date column and in specific other columns) and paste it all in another workbook which has formulae and macros for analysis of the data. I had everything else working fine for a specific date but was informed that a date range was required. Your suggested correction has helped me get it done. Thanks, Jim -- Pops Jackson "Tom Ogilvy" wrote: is this just illustrative or are firstdate and seconddate actually string variables containing the date in the correct format. If the latter Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="=" & firstdate, _ Operator:=xlAnd, Criteria2:="<=" & seconddate -- Regards, Tom Ogilvy "Pops Jackson" wrote: I have gotten frustrated with trying to get my code to work autofiltering date ranges. The code I am using includes: Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="=firstdate", Operator:=xlAnd, Criteria2:="<=seconddate" The result is that the entire range is hidden. How can I get the default autofilter dialog to show? I have decided this is the best solution unless the code I am using can be altered successfully. Thanks, Jim -- Pops Jackson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Opening the Autofilter dialog box
Thanks, Aidan.
Your suggestion also gives me the needed help. To you and Tom, thank you very much for the quick responses. By the time I actually post a question, I have tried everything I can think of and have combed the existing posts to find one that already has addressed my problem. So I am at the "desperate" level when I post and quick responses are invaluable. Thanks again, Jim -- Pops Jackson " wrote: I think the problem is in the use of variables - you have specified literal text, so the filter is happening on the TEXT - try something like dim StartDate as string, secondDate as string StartDate="="& format(whateverdate,whateverformat) Selection.AutoFilter Field:=1, Criteria1:=StartDate etc Pops Jackson wrote: I have gotten frustrated with trying to get my code to work autofiltering date ranges. The code I am using includes: Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="=firstdate", Operator:=xlAnd, Criteria2:="<=seconddate" The result is that the entire range is hidden. How can I get the default autofilter dialog to show? I have decided this is the best solution unless the code I am using can be altered successfully. Thanks, Jim -- Pops Jackson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Opening the Autofilter dialog box
You could do it without filtering if you are copying cells by code, by
checking the date in the cell against the bounds, and only if they match copying them over - I'd do it with OFFSET and a counter variable as sometimes on our machines the lastcell doesn't return the last cell (or just refuses to work at all!) - but as long as you have a working solution, our job is done! Pops Jackson wrote: Thanks, Aidan. Your suggestion also gives me the needed help. To you and Tom, thank you very much for the quick responses. By the time I actually post a question, I have tried everything I can think of and have combed the existing posts to find one that already has addressed my problem. So I am at the "desperate" level when I post and quick responses are invaluable. Thanks again, Jim -- Pops Jackson " wrote: I think the problem is in the use of variables - you have specified literal text, so the filter is happening on the TEXT - try something like dim StartDate as string, secondDate as string StartDate="="& format(whateverdate,whateverformat) Selection.AutoFilter Field:=1, Criteria1:=StartDate etc Pops Jackson wrote: I have gotten frustrated with trying to get my code to work autofiltering date ranges. The code I am using includes: Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="=firstdate", Operator:=xlAnd, Criteria2:="<=seconddate" The result is that the entire range is hidden. How can I get the default autofilter dialog to show? I have decided this is the best solution unless the code I am using can be altered successfully. Thanks, Jim -- Pops Jackson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| autofilter dialog boxes - can i call this up in the | Excel Worksheet Functions | |||
| Dialog box upon opening workbook | Excel Worksheet Functions | |||
| Opening Built-In Custom AutoFilter Dialog Box Programmatically | Excel Programming | |||
| Opening a dialog | Excel Programming | |||
| AutoFilter Custom Dialog | Excel Programming | |||
Linear Mode
