Deleting a row if closing date elapsed

B

bollard

Hello

I want to delete a row if the closing date shown in one column of that row
is equal to, or before today's date.

Can anyone offer the correct conditional format, or is it a job for a Macro?

Not very well up on Macros, so please treat me as a numbskull!!!
 
M

Max

One way is to use autofilter ..

Try this on a spare copy:
Autofilter on the dates col, choose "Custom"
In the dialog, select: less than or equal to,
then type in today's date into the box, eg: 4/27/2007 (key in the full date)
Click OK. This should filter out all rows to be deleted. Simply select all
the filtered row headers, right-click > Delete Row.
 
B

bollard

Hi Max

I really want the row to delete automatically once we reach the closing
date. Can you help with that, please?
 
M

Max

Welcome back <g> ..

One way to achieve it, which extracts only the current cases in a new sheet
....

Source data is assumed input continuously in a sheet: X,
within cols A to D, data from row2 down,
where the key col = col A (real dates)

In a new sheet: Current Cases,
Copy n paste the same col labels in X into B1:E1

Put in A2:
=IF(X!A2="","",IF(X!A2<=TODAY(),"",ROW()))
Leave A1 blank

Put in B2:
=IF(ROW(A1)>COUNT($A:$A),"",INDEX(X!A:A,SMALL($A:$A,ROW(A1))))

Copy B2 across to E2. Select A2:E2, fill down to cover the max expected
extent of source data in X, eg down to E200. Format col B as date. The above
will auto-return the required results, ie only the source lines from X with
dates > today (ie the current/unexpired cases), with all lines neatly bunched
at the top.
 
Top