Delete row based upon Date

[patricevease]

Hi all.

In column F, starting from row 11, I have a list of dates.

Does anyone know how to create a VBA that will delete the entire row the date is in if the date is a week old... " =Today()-7 " is the cell function for this if that helps.

Thanks a lot in advance for anyone that can help.

[SamT]

Sub VBAX_SamT_DeleteWeekOld()
Dim r As Long

'Must delete Rows from bottom up
For r = Cells(Rows.Count, "F").End(xlUp).Row to 11 Step - 1
If CDate(Cells(r, "F")) < Date - 7 Then Rows(r).Delete
Next r
End Sub

[Aussiebear]

@ Sam. Just wondering your formula is incorrect? Should it be ">Date-7 " to be a week old?

[patricevease]

Sub VBAX_SamT_DeleteWeekOld()
Dim r As Long

'Must delete Rows from bottom up
For r = Cells(Rows.Count, "F").End(xlUp).Row to 11 Step - 1
If CDate(Cells(r, "F")) < Date - 7 Then Rows(r).Delete
Next r
End Sub

Hi SamT thank you so much for this!

If you wouldn't mind answering a further question:
I understand what the script is doing (deleting all rows below f11 with old dates), however this is creating a problem as it is deleting empty rows and ruining the format.

Is it possible for the script to search for the last entry in column F (starting at f11), use this as the range, and then only delete rows within this range where the criteria applies? I.e if the the last entry was in F17 the script would recognize this, use f11-f17 as the range and then delete the dates within this.

Thanks in advance, really appreciate it.

[SamT]

You have data below the dates? Oops.
If there are no empty cells in the dates range in column F...

Change
Cells(Rows.Count, "F").End(xlUp).RowCells(Rows.Count, "F").End(xlUp).Row
to
Range("F11").End(xldown).Row

IF Column F has empty cells in the table Change the Range Column to one that will never have empty cells.

[SamT]

Ted, > Date - 7 is newer than a week. < Date - 7 is older than a week.

[Aussiebear]

Yep Sam, this is why I questioned your reply. The OP is asking if a date is a week old, not less than a week.

[SamT]

That's what I gave. Older than one week

Today is the 16th.
To delete the 3rd use < 16 - 7 [3 is less than 9]
To delete the 10th use > 16 - 7 [10 is greater than 9]
To delete the 9th use =< 16 - 7 or < 16 - 6

I left it to the OP to decide if they want to keep Rows that are 8 days old (= Date - 7) because the original formula they gave was "Older than one week and one day."

[shailendranr]

You can use a worksheetfunction.weeknumber formula with do until loop/For loop

ex:
sub delet_code()

range("f11").select
do until activecell.value=""

if worksheetfunction.weeknumber(activecell.value)<worksheetfunction.weeknumber(Date) then
activecell.entirerow.delete
else
activecell.offset(1,0).select
endif

loop

End sub

[Aussiebear]

Since I'm in my natural state of confusion...... which is in't hard to do. Lets assume that a date in (r, F) could potentially be high 20's and the next week could be low single digits how does your code work Sam?

[SamT]

Dates, and Date+Time),) are stored as doubles. Date are actually the number of days from a certain date in the past (1/1/1900 or 1/1/1904, usually 1904.) Times are the decimal fraction of a day. 6AM = .25, Noon = .5, 6Pm = .75) One minute = 1/24*60, One Second = 1/24*60*60

Today, 2/19/16 is day 1/1/04, (Day Zero) + 42419 days
MsgBox Format(0, "mm-dd-yy")
MsgBox Format(42419, "mm-dd-yy")


Enter a date in a Cell, format the next cell as a Number. Copy the Date cell and PasteSpecial Values into the Number cell.

[Aussiebear]

I've just spent the day out on the water (burnt, tired and nil fish), on opening this thread I have to agree your logic is good.

[SamT]

No Fish! Blame EL Nino!

How's your navigation spreadsheet working?

[Aussiebear]

Sigh...... none to well but that another topic