Help with Advanced Filter Comparison Criteria

[Aaron Vowell]

I have a spreadsheet that lists database records,
and the date the record was opened. (New records
are opened every day, so I have a query set up to
retrieve all the records.) Example:

A B
1 Number Date
2 1156 3/9/2004 12:16:18 PM
3 1250 11/4/2003 1:44:12 PM

I want to filter the list of records to show only
the records that were opened in the last 10 days.
I have been trying to use an advanced filter to perform
this task. I inserted 4 rows above my data and copied
the column headings to the first row. In B2, I
inserted the =Now() function. In B3 I calculated
=$b$2-10. My spreadsheet looks like this:

A B
1 Number Date
2 4/30/04 18:09
3 4/20/04 18:09
4
5 Number Date
6 1156 3/9/2004 12:16:18 PM
7 1250 11/4/2003 1:44:12 PM

I place the cursor in A6 and click:
Data>Filter>Advanced Filter

The list range shows up as $A$5:$P$709 (obviously
I abbreviated the example). I have tried several
entries for the criteria range without success.

What should I put in the criteria range to sort
the list the way I want sorted?

Aaron

 

[Debra Dalgleish]

In cell C2, enter the following formula:

=AND(B6>=$B$3,B6<=$B$2)

Leave cell C1 blank -- when you use formulas in the criteria range,
either leave the heading cell blank, or use a heading that is different
than the column heading in the table.

When you run the Advanced Filter, select cells C1:C2 as the criteria range.

 

[Debra Dalgleish]

At the end of the macro code, write the formula into the criteria cell.
For example:

Dim ws As Worksheet
Set ws = Sheets("Sheet1")
ws.Cells(2, 3).Formula = "=AND(B6>=$B$2,B6<=$B$3)"