filters

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

HELP I want to create a simple filter in excel 2003.

In column 2 I have a list of dates (in order).
Rows 1-5 I have titles of columns etc...

What I need is from row 6 down I want it to display only the records
starting from yesterday, and hide all the older records.

Many thanks to all.
 
Select your range (Row 5 to the last row) and all the columns.
Then use:
Data|filter|Autofilter

You can the dropdown arrow to do a Custom filter.
greater than or equal to
yesterday's date (either type it or choose from the list).
 
Hi Steve,

You can use AutoFilter. (menu item Data -> Filter -> Autofilter.

Normally you can just select any cell in the range and set AutoFilter but
this will place the dropdown arrows in the first row.

However, if I understand your question correctly, you are using 5 rows for
the column headers and therefore you need to select row 5 first before
setting Autofilter so that Excel thinks that only row 5 is the header row for
the AutoFilter.

Then to get all records starting yesterday click the dropdown arrow on the
appropriate column and select Custom -> Is Greater than or Equal to -> then
in the adjacent column select yesterday's date.
 
Hi,

1. You can only pick yesterday's date from the list on the right if one of
your dates is yesterday.
2. Each day you will need to change the autofilter date.
3. To avoid reenteing the date you can use the Advanced Filter option:
- set up the following criteria area, say in J1:J2
J1: Dates
J2: =">="&TODAY()
Where Dates is the first title above the actual dates.

Select the data starting with row 5 and going down as far as your data (as
discussed in the previous posts) If the dates are in column A with the title
"Dates" in A5 you would select from A5 down.

Choose Data, Filter, Advanced Filter.
If necessary indicate the List range as discussed above
In the Criteria range box highlight or enter J1:J2
Click OK.

The criteria range will update automatically every day, so to rerun the
filter just choose Data, Filter, Advanced Filter, OK.
 
One way is by using data>filter>autofilter and the other way is by the
use of macro as specified follows

1.Right click on toolbar>select control box
2.From the control box that appears on the sheet draw a command button
on the sheet
3.Double click the command button to open the code window and paste
following codes
Code: 
Private Sub CommandButton1_Click()
Dim row As Integer, col As Integer
row = 6
col = 2

Dim dt As Date

While Sheet2.Cells(row, col).Value <> ""
dt = CDate(Sheet2.Cells(row, col).Value)

If dt >= Now Then
Sheet2.Rows(row).Delete
row = row - 1
End If

row = row + 1
Wend
End Sub
hope this helps.....


Chris
 
Back
Top