Date Filter?!?
 

I have data that looks like this:
01/01/2000 XXX YYY
01/02/2000 XXX YYY
01/02/2000 XXX YYY
01/03/2000 XXX YYY
.....(yes there are multiple records per date)

I would like to filter out the data that isn't in a certain date range. The
user will be entering date1 and date2 (begining and end dates) and apply
other macros to the remaining entries.

I recorded the macro and used autofilter and custom with and < dates but I
can't seem to change the code to allow a user entered variable as the
criteria (returns 0 fields)

I am sure that I could just sort wrt date and step through and exclude
entries that don't have a valid date but I thought this would be easier.
Help on either approach would be great.

Date Filter?!?
 

Hi

Create another sheet, where you insert your criteria at top of sheet.
P.e. B1=StartDate, D1=EndDate

Below this create an table like the original one, but with formulas instead
of values. P.e. when your first datarow on original table is in range
Sheet1!A2:C2, then on new sheet:
Into range A2:C2 enter headers (Date, Data1, Data2)
A3=IF(OR(Sheet1!A2="",Sheet1!$A2<$B$1,Sheet1!$A2$ D$1),"",Sheet1!A2)
Copy the formula you entered to range matching the original table, pluss
some amount of spare rows when you foresee that you'll add data into
original table.
Select the range on new sheet, starting with headers and including all rows
with formulas (you can have all columns selected, or only column A), and set
autofilter on.
Enter your criteria into B1 and D1, and set autofilter for column A to Not
Empty. Every time you reset your criteria, reset the autofilter.

--
Arvi Laanemets
(Don't use my reply address - it's spam-trap)


"Joe Blow" wrote in message
news:LpWmc.382243$Pk3.174767@pd7tw1no...
I have data that looks like this:
01/01/2000 XXX YYY
01/02/2000 XXX YYY
01/02/2000 XXX YYY
01/03/2000 XXX YYY
....(yes there are multiple records per date)

I would like to filter out the data that isn't in a certain date range.
The
user will be entering date1 and date2 (begining and end dates) and apply
other macros to the remaining entries.

I recorded the macro and used autofilter and custom with and < dates but
I
can't seem to change the code to allow a user entered variable as the
criteria (returns 0 fields)

I am sure that I could just sort wrt date and step through and exclude
entries that don't have a valid date but I thought this would be easier.
Help on either approach would be great.

Date Filter?!?
 

Joe, i just tried this:

Sub Macro4()

Dim strstartdate
Dim strenddate

strstartdate = Range("startdate")
strenddate = Range("enddate")

Range("database").Select
Selection.AutoFilter Field:=1, Criteria1:="" & strstartdate,
Operator:=xlAnd _
, Criteria2:="<" & strenddate

End Sub

which has three named ranges on the worksheet: "Datadase" is the list
to filter;"startdate" is where the user inputs the first
date;"enddate" is the last date for the filter.
It doesn't throw an error but it filters everything out!!
I'd like to know the answer an' all!!!

Sorry I'm no more help
Jason



"Joe Blow" wrote in message news:<LpWmc.382243$Pk3.174767@pd7tw1no...
I have data that looks like this:
01/01/2000 XXX YYY
01/02/2000 XXX YYY
01/02/2000 XXX YYY
01/03/2000 XXX YYY
....(yes there are multiple records per date)

I would like to filter out the data that isn't in a certain date range. The
user will be entering date1 and date2 (begining and end dates) and apply
other macros to the remaining entries.

I recorded the macro and used autofilter and custom with and < dates but I
can't seem to change the code to allow a user entered variable as the
criteria (returns 0 fields)

I am sure that I could just sort wrt date and step through and exclude
entries that don't have a valid date but I thought this would be easier.
Help on either approach would be great.