Opening the Autofilter dialog box

[Pops Jackson]

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

 

[Tom Ogilvy]

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

 

[aidan.heritage]

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]

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

 

[Tom Ogilvy]

Pops jackson -
Great, glad it worked!

 

[Pops Jackson]

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

 

[aidan.heritage]

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!