Thread: 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

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

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

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

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

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


"aidan.heritage@virgin.net" 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
>
>

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
>
>
> "aidan.heritage@virgin.net" 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
> >
> >