Delete Date

[Willih]

I'm working with a spreadsheet that will have many rows of data and I want to delete any row that has todays date in Column F. Column F can consist of several different dates but I would like to delete todays date which will change every day.

With "today's date" changing every day I'm sure I will have to use the =TODAY() formula somewhere in the macro. Here is a simple equation of what I'm trying to get to:

Delete any date in column F that = (=TODAY())

Any suggestions.

[Richard Buttrey]

Hi,

No need for a macro, just use a helper column. Put =Today() in a cell somewhere and name it say dtToday

Now in the helper column alongside your data enter the formula

=If(F1=dtToday,"Delete","Keep")

copy this down your data, filter on the helper column for the word delete and then select and delete the filtered rows, finally turning off the filter.

HTH

[Paul]

Hi Willih, you might try:

Sub WILLIH()
Dim i As Long, LR As Long
LR = Range("F" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Cells(i, "F").Value = Date Then Cells(i, "F").EntireRow.Delete
Next i
End Sub

[Willih]

Hi Paul

Thanks for replying. If I wanted to use the above code in a macro that I would be running everyday but I wanted to delete any data rows with the current days date, how can I make the date always be the current days date? This is where I thought I would have to use the =today() formula to always make the date field equal the current day. Is that true?

[Paul]

In the bit of code:

If Cells(i, "F").Value = Date

The "Date" function returns the current date. So if you run it tomorrow, it will check against tomorrow's date, etc.

[Willih]

Another date question. If I want to use the WEEKDAY formula in a spreadsheet where I have todays date in (E2) and I want (F2) to show (E2) + 3 days but skip weekends (Sat & Sun). I'm using the =today() formula to get todays date in (E2). I also created a WEEKDAY table on sheet 3 which I didn't know you had to have. I can't get F2 to show E2 + 3 days. Example: (E2) = 9/2/10 (F2) should = 9/7/10.

Also if it's possible I would like to exclude holidays. So using the same example (E2) = 9/2/10 (F2) should = 9/8/10 because 9/6/10 is a holiday.

Any suggestions?

[Paul]

To skip weekends (and even holidays if you have a list of them), use the WORKDAY function.

If E2 has 9/2/10 and E3 has 3 (number of days to add), then in F2 use

=WORKDAY(E2,E3)

The third argument of WORKDAY handles holidays, but you must have a table of the holidays somewhere in your workbook. If you did, you could then use something like:

=WORKDAY(E2,E3,Sheet7!$A$1:$A$10)

Where Sheet7 cells A1:A10 contains your holiday dates.

[Willih]

Thanks Paul

Everything work great.

[Cheeky Charlie]

todays date which will change every day.

o rly?

If your spreadsheet is large, rather than run through every cell, filter for today's date and delete en masse, something like:

range(range("A1").end(xldown),range("A1").end(xltoright)).autofilter 6, date
range("2:" & rows.count).delete