Hi hope this is possible.

I would like to run a macro that uses a custom filter to do the
following


a Date is enetered into j18 (start date) and a date entered into k18
(end date)

is it possible to use auto filter to run a custom macro where as the
criterias are

>= j18 and < k18


How would i accomplish this,

any help with some code would be useful i tried the following without
much success

Sub daterange()

Selection.AutoFilter Field:=2, Criteria1:=">=Cell(j18)",
Operator:= _
xlAnd, Criteria2:="<Cell(j18)"
End Sub




HYCH

Steve

On Mar 3, 3:51 pm, "K1KKKA" <instruct...@hotmail.com> wrote:
> Hi hope this is possible.
>
> I would like to run a macro that uses a custom filter to do the
> following
>
> a Date is enetered into j18 (start date) and a date entered into k18
> (end date)
>
> is it possible to use auto filter to run a custom macro where as the
> criterias are
>
> >= j18 and < k18
>
> How would i accomplish this,
>
> any help with some code would be useful i tried the following without
> much success
>
> Sub daterange()
>
> Selection.AutoFilter Field:=2, Criteria1:=">=Cell(j18)",
> Operator:= _
> xlAnd, Criteria2:="<Cell(j18)"
> End Sub
>
> HYCH
>
> Steve

Only just considered that maybe a worksheet change event might be more
beneficial??

Steve,

First of all, if the J18 criteria is both >= AND <, you would get all the records.

Try this form:

Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("j18")

It's a good idea to name cells like J18, so if they get moved, the macro won't still be
looking for the old cell. If J18 is named CR1, then it would look like:

Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("CR1")

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"K1KKKA" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Mar 3, 3:51 pm, "K1KKKA" <instruct...@hotmail.com> wrote:
>> Hi hope this is possible.
>>
>> I would like to run a macro that uses a custom filter to do the
>> following
>>
>> a Date is enetered into j18 (start date) and a date entered into k18
>> (end date)
>>
>> is it possible to use auto filter to run a custom macro where as the
>> criterias are
>>
>> >= j18 and < k18
>>
>> How would i accomplish this,
>>
>> any help with some code would be useful i tried the following without
>> much success
>>
>> Sub daterange()
>>
>> Selection.AutoFilter Field:=2, Criteria1:=">=Cell(j18)",
>> Operator:= _
>> xlAnd, Criteria2:="<Cell(j18)"
>> End Sub
>>
>> HYCH
>>
>> Steve
>
> Only just considered that maybe a worksheet change event might be more
> beneficial??
>

On Mar 3, 4:13 pm, "Earl Kiosterud" <some...@nowhere.com> wrote:
> Steve,
>
> First of all, if the J18 criteria is both >= AND <, you would get all the records.
>
> Try this form:
>
> Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("j18")
>
> It's a good idea to name cells like J18, so if they get moved, the macro won't still be
> looking for the old cell. If J18 is named CR1, then it would look like:
>
> Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("CR1")
>
> --
> Earl Kiosterudwww.smokeylake.com
> -----------------------------------------------------------------------"K1KKKA" <instruct...@hotmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > On Mar 3, 3:51 pm, "K1KKKA" <instruct...@hotmail.com> wrote:
> >> Hi hope this is possible.
>
> >> I would like to run a macro that uses a custom filter to do the
> >> following
>
> >> a Date is enetered into j18 (start date) and a date entered into k18
> >> (end date)
>
> >> is it possible to use auto filter to run a custom macro where as the
> >> criterias are
>
> >> >= j18 and < k18
>
> >> How would i accomplish this,
>
> >> any help with some code would be useful i tried the following without
> >> much success
>
> >> Sub daterange()
>
> >> Selection.AutoFilter Field:=2, Criteria1:=">=Cell(j18)",
> >> Operator:= _
> >> xlAnd, Criteria2:="<Cell(j18)"
> >> End Sub
>
> >> HYCH
>
> >> Steve
>
> > Only just considered that maybe a worksheet change event might be more
> > beneficial??- Hide quoted text -
>
> - Show quoted text -

Thanks Earl,

Have renamed the relevant cells as Date1 and Date2

Am trying to get the range to recognise the following,
>= Date1
< Date2


Have tried the code below using your suggestion, but to no avail

Could you have a look at and advise please

Sub Daterange1()
Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("Date1"),
Operator _
:=xlAnd, Criteria2:="<" & Range("Date2")
End Sub

Thanks Steve

Sometimes converting to longs helps:

...., Criteria1:=">=" & clng(Range("Date1").value), ...

or even using the same numberformat as you see on the worksheet:

...., Criteria1:=">=" & format(Range("Date1").value, "mm/dd/yyyy"), ...

(adjust that format to match)

K1KKKA wrote:
>
> On Mar 3, 4:13 pm, "Earl Kiosterud" <some...@nowhere.com> wrote:
> > Steve,
> >
> > First of all, if the J18 criteria is both >= AND <, you would get all the records.
> >
> > Try this form:
> >
> > Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("j18")
> >
> > It's a good idea to name cells like J18, so if they get moved, the macro won't still be
> > looking for the old cell. If J18 is named CR1, then it would look like:
> >
> > Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("CR1")
> >
> > --
> > Earl Kiosterudwww.smokeylake.com
> > -----------------------------------------------------------------------"K1KKKA" <instruct...@hotmail.com> wrote in message
> >
> > news:(E-Mail Removed)...
> >
> >
> >
> > > On Mar 3, 3:51 pm, "K1KKKA" <instruct...@hotmail.com> wrote:
> > >> Hi hope this is possible.
> >
> > >> I would like to run a macro that uses a custom filter to do the
> > >> following
> >
> > >> a Date is enetered into j18 (start date) and a date entered into k18
> > >> (end date)
> >
> > >> is it possible to use auto filter to run a custom macro where as the
> > >> criterias are
> >
> > >> >= j18 and < k18
> >
> > >> How would i accomplish this,
> >
> > >> any help with some code would be useful i tried the following without
> > >> much success
> >
> > >> Sub daterange()
> >
> > >> Selection.AutoFilter Field:=2, Criteria1:=">=Cell(j18)",
> > >> Operator:= _
> > >> xlAnd, Criteria2:="<Cell(j18)"
> > >> End Sub
> >
> > >> HYCH
> >
> > >> Steve
> >
> > > Only just considered that maybe a worksheet change event might be more
> > > beneficial??- Hide quoted text -
> >
> > - Show quoted text -
>
> Thanks Earl,
>
> Have renamed the relevant cells as Date1 and Date2
>
> Am trying to get the range to recognise the following,
> >= Date1
> < Date2
>
> Have tried the code below using your suggestion, but to no avail
>
> Could you have a look at and advise please
>
> Sub Daterange1()
> Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("Date1"),
> Operator _
> :=xlAnd, Criteria2:="<" & Range("Date2")
> End Sub
>
> Thanks Steve

--

Dave Peterson

try this idea
With Range("A6:H6")
.AutoFilter
.AutoFilter Field:=1, Criteria1:=">=" _
& Range("n1") & "", Operator:=xlAnd _
, Criteria2:="<=" & Range("n2")
End With

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"K1KKKA" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi hope this is possible.
>
> I would like to run a macro that uses a custom filter to do the
> following
>
>
> a Date is enetered into j18 (start date) and a date entered into k18
> (end date)
>
> is it possible to use auto filter to run a custom macro where as the
> criterias are
>
>>= j18 and < k18
>
>
> How would i accomplish this,
>
> any help with some code would be useful i tried the following without
> much success
>
> Sub daterange()
>
> Selection.AutoFilter Field:=2, Criteria1:=">=Cell(j18)",
> Operator:= _
> xlAnd, Criteria2:="<Cell(j18)"
> End Sub
>
>
>
>
> HYCH
>
> Steve
>

-----------------------------------------------------------------------
"K1KKKA" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Mar 3, 4:13 pm, "Earl Kiosterud" <some...@nowhere.com> wrote:
>> Steve,
>>
>> First of all, if the J18 criteria is both >= AND <, you would get all the records.
>>
>> Try this form:
>>
>> Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("j18")
>>
>> It's a good idea to name cells like J18, so if they get moved, the macro won't still be
>> looking for the old cell. If J18 is named CR1, then it would look like:
>>
>> Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("CR1")
>>
>> --
>> Earl Kiosterudwww.smokeylake.com
>> -----------------------------------------------------------------------"K1KKKA"
>> <instruct...@hotmail.com> wrote in message
>>
>> news:(E-Mail Removed)...
>>
>>
>>
>> > On Mar 3, 3:51 pm, "K1KKKA" <instruct...@hotmail.com> wrote:
>> >> Hi hope this is possible.
>>
>> >> I would like to run a macro that uses a custom filter to do the
>> >> following
>>
>> >> a Date is enetered into j18 (start date) and a date entered into k18
>> >> (end date)
>>
>> >> is it possible to use auto filter to run a custom macro where as the
>> >> criterias are
>>
>> >> >= j18 and < k18
>>
>> >> How would i accomplish this,
>>
>> >> any help with some code would be useful i tried the following without
>> >> much success
>>
>> >> Sub daterange()
>>
>> >> Selection.AutoFilter Field:=2, Criteria1:=">=Cell(j18)",
>> >> Operator:= _
>> >> xlAnd, Criteria2:="<Cell(j18)"
>> >> End Sub
>>
>> >> HYCH
>>
>> >> Steve
>>
>> > Only just considered that maybe a worksheet change event might be more
>> > beneficial??- Hide quoted text -
>>
>> - Show quoted text -
>
> Thanks Earl,
>
> Have renamed the relevant cells as Date1 and Date2
>
> Am trying to get the range to recognise the following,
>>= Date1
> < Date2
>
>
> Have tried the code below using your suggestion, but to no avail
>
> Could you have a look at and advise please
>
> Sub Daterange1()
> Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("Date1"),
> Operator _
> :=xlAnd, Criteria2:="<" & Range("Date2")
> End Sub
>
> Thanks Steve


Steve,

This code works for me. I get the records between the dates specified in J18 and J19. It
doesn't matter how the dates are formatted either in the table or in the criteria cells.

Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("j18"), Operator:=xlAnd,
Criteria2:="<" & Range("J19")

Be sure that the stuff in the table and in your criteria cells are truly dates. If you
remove all formatting (Edit - Clear - Formats), you should see numbers. Feb 1, 2007 is
39114. Etc.

Say what does or doesn't happen when you run your code. Error messages? We're a bit in the
dark.

--
Earl Kiosterud
www.smokeylake.com

On Mar 3, 8:30 pm, "Earl Kiosterud" <some...@nowhere.com> wrote:
> -----------------------------------------------------------------------"K1KKKA" <instruct...@hotmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
>
>
> > On Mar 3, 4:13 pm, "Earl Kiosterud" <some...@nowhere.com> wrote:
> >> Steve,
>
> >> First of all, if the J18 criteria is both >= AND <, you would get all the records.
>
> >> Try this form:
>
> >> Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("j18")
>
> >> It's a good idea to name cells like J18, so if they get moved, the macro won't still be
> >> looking for the old cell. If J18 is named CR1, then it would look like:
>
> >> Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("CR1")
>
> >> --
> >> Earl Kiosterudwww.smokeylake.com
> >> -----------------------------------------------------------------------"K1K*KKA"
> >> <instruct...@hotmail.com> wrote in message
>
> >>news:(E-Mail Removed)...
>
> >> > On Mar 3, 3:51 pm, "K1KKKA" <instruct...@hotmail.com> wrote:
> >> >> Hi hope this is possible.
>
> >> >> I would like to run a macro that uses a custom filter to do the
> >> >> following
>
> >> >> a Date is enetered into j18 (start date) and a date entered into k18
> >> >> (end date)
>
> >> >> is it possible to use auto filter to run a custom macro where as the
> >> >> criterias are
>
> >> >> >= j18 and < k18
>
> >> >> How would i accomplish this,
>
> >> >> any help with some code would be useful i tried the following without
> >> >> much success
>
> >> >> Sub daterange()
>
> >> >> Selection.AutoFilter Field:=2, Criteria1:=">=Cell(j18)",
> >> >> Operator:= _
> >> >> xlAnd, Criteria2:="<Cell(j18)"
> >> >> End Sub
>
> >> >> HYCH
>
> >> >> Steve
>
> >> > Only just considered that maybe a worksheet change event might be more
> >> > beneficial??- Hide quoted text -
>
> >> - Show quoted text -
>
> > Thanks Earl,
>
> > Have renamed the relevant cells as Date1 and Date2
>
> > Am trying to get the range to recognise the following,
> >>= Date1
> > < Date2
>
> > Have tried the code below using your suggestion, but to no avail
>
> > Could you have a look at and advise please
>
> > Sub Daterange1()
> > Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("Date1"),
> > Operator _
> > :=xlAnd, Criteria2:="<" & Range("Date2")
> > End Sub
>
> > Thanks Steve
>
> Steve,
>
> This code works for me. I get the records between the dates specified inJ18 and J19. It
> doesn't matter how the dates are formatted either in the table or in the criteria cells.
>
> Selection.AutoFilter Field:=2, Criteria1:=">=" & Range("j18"), Operator:=xlAnd,
> Criteria2:="<" & Range("J19")
>
> Be sure that the stuff in the table and in your criteria cells are truly dates. If you
> remove all formatting (Edit - Clear - Formats), you should see numbers. Feb 1, 2007 is
> 39114. Etc.
>
> Say what does or doesn't happen when you run your code. Error messages? We're a bit in the
> dark.
>
> --
> Earl Kiosterudwww.smokeylake.com- Hide quoted text -
>
> - Show quoted text -

Earl, not sure what happened earlier, but thanks for it works fine
now.


Steve