View Full Version : Delete row based upon Date
patricevease
02-15-2016, 08:18 AM
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.
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
02-16-2016, 02:59 AM
@ Sam. Just wondering your formula is incorrect? Should it be ">Date-7 " to be a week old?
patricevease
02-16-2016, 03:10 AM
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.
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.
Ted, > Date - 7 is newer than a week. < Date - 7 is older than a week.
Aussiebear
02-16-2016, 05:52 PM
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.
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
02-19-2016, 03:16 AM
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
02-19-2016, 04:19 AM
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?
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
02-20-2016, 01:49 AM
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.
No Fish! Blame EL Nino!
How's your navigation spreadsheet working?
Aussiebear
02-21-2016, 03:32 AM
Sigh...... none to well but that another topic
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.