Input Box
 

How do I set the criteria for the filter?

I will get a date from the input box, how do I get this into the filter part
of the macro?

Thanks.
"Tom Ogilvy" wrote in message
...
click in the first line of the sheet (assume row1 with headers in that
row)
and do Data=Filter=Autofilter

in the dropdown in Column B, select custom and in the resulting dialog,
select Is less than, then put in the date that would be the first date not
deleted.

Now select all the rows but the header row and do Edit=Delete

Only visible rows will be delete

then Data=Filter=Autofilter to turn off the filter

You can turn on the macro recorder when you do this. Then if you want to
add a input box and set the filter criteria using that, you can just
modify
the recorded code.

--
Regards,
Tom Ogilvy


"Gareth" wrote in message
...
I have a sheet of data made up of telephone enquiries.

It starts on the 01/01/2002 and runs to today.

What I want is code to show an input box asking the user for a date
(usually
today but not always). I then want all enqiries that are more than a
year
older than the date entered to be deleted from the sheet.

The sheet is called "Enquiries = 01/01/2002" and the date is in column
B.

Input Box
 

After recording

Criteria1:="<=01/15/2003"

so

res = Cdate(Inputbox("enter date"))

.. . .

Criteria1:="<=" & clng(res)
--
Regards,
Tom Ogilvy


Gareth wrote in message
...
How do I set the criteria for the filter?

I will get a date from the input box, how do I get this into the filter
part
of the macro?

Thanks.
"Tom Ogilvy" wrote in message
...
click in the first line of the sheet (assume row1 with headers in that
row)
and do Data=Filter=Autofilter

in the dropdown in Column B, select custom and in the resulting dialog,
select Is less than, then put in the date that would be the first date
not
deleted.

Now select all the rows but the header row and do Edit=Delete

Only visible rows will be delete

then Data=Filter=Autofilter to turn off the filter

You can turn on the macro recorder when you do this. Then if you want
to
add a input box and set the filter criteria using that, you can just
modify
the recorded code.

--
Regards,
Tom Ogilvy


"Gareth" wrote in message
...
I have a sheet of data made up of telephone enquiries.

It starts on the 01/01/2002 and runs to today.

What I want is code to show an input box asking the user for a date
(usually
today but not always). I then want all enqiries that are more than a
year
older than the date entered to be deleted from the sheet.

The sheet is called "Enquiries = 01/01/2002" and the date is in
column
B.

I imagined some kinf of filter then delete but was unable to code it.

Thanks in advance.

Gareth