Macro that deletes rows based on date and time

Question

Hi, I'm still looking to try and add to the last lines of my macro (the whole macro listed below) I've got:

lr = Cells(Rows.Count, "C").End(xlUp).Row 'find last row
For i = lr To 2 Step -1 'loop thru backwards, finish at 2 for headers
If Cells(i, "C").Text = "#N/A" Then Rows(i).EntireRow.Delete
Next i

which works to find any NA's and deletes those entire rows and I was trying to do something similar with columns U and V, where by it deletes any entire rows where the date is after the date on the front of the report (Summary J3) and after a certain time (Reference Sheet c12), Or any dates that are earlier the date in Summary G3 Date and after a certain time (reference sheet C13)

So far I've tried to recreate the code that works for deleting NA's with the below with no cigar:

lrU = Cells(Rows.Count, "U").End(xlUp).Row 'find last row
LrV = Cells(Rows.Count, "V").End(xlUp).Row 'find last row
For i = lrU To 2 Step -1 'loop thru backwards, finish at 2 for headers
'column "U" is column21
If Cells(i, 21).Value < Worksheets("Summary").Cells(3, 7).Value And _
Cells(i, 22).Value < Worksheets("Reference Sheet").Cells(12, 4).Value Or _
Cells(i, 21).Value > Worksheets("Summary").Cells(3, 10).Value And _
Cells(i, 22).Value > Worksheets("Reference Sheet").Cells(13, 4).Value Then
Rows(i).EntireRow.Delete

mtarler · Answer

clh_1496&nbsp; I'm not going to parse through all your code right now but a quick look at the section of interest.&nbsp;But First:you say "date is after the date ... and after a certain time ..." so are you comparing DATE/DAY in once case and TIME in the other?&nbsp; If so you have to look at the whole number value for the DATE comparison and the fractional portion for the TIME.Next:you say "date is after the date on the front of the report (Summary J3) and after a certain time (Reference Sheet c12), Or any dates that are earlier the date in Summary G3 Date and after a certain time (reference sheet C13)" BUT your lines indicate &lt;G3 AND &lt;D12&nbsp; &nbsp;OR&nbsp; &gt;J3 AND &gt;D13 ,&nbsp; So first off you have an issue between column D and column C and then the grouping is mixed up AND you don't use any () to group them so the order of operations my not act the way you think.I would suggest:lrU = Cells(Rows.Count, "U").End(xlUp).Row 'find last rowLrV = Cells(Rows.Count, "V").End(xlUp).Row 'find last row&nbsp; 'not used and assume both columns same lengthDate1=Worksheets("Summary").Cells(3, 10).Value2Time1=Worksheets("Reference Sheet").Cells(12, 3).Value2Date2=Worksheets("Summary").Cells(3, 7).Value2Time2=Worksheets("Reference Sheet").Cells(13, 4).Value2For i = lrU To 2 Step -1 'loop thru backwards, finish at 2 for headers'column "U" is column21If (&nbsp; ( (Cells(i, 21).Value &lt; Date2) And (Cells(i, 22).Value &lt; Time2) ) Or _&nbsp; &nbsp; &nbsp; ( (Cells(i, 21).Value &gt; Date1)&nbsp;And&nbsp;(Cells(i, 22).Value &gt; Time2) ) )&nbsp;ThenRows(i).EntireRow.Delete

clh_1496 · Answer

mtarler&nbsp;&nbsp;I've tried your code (edited slightly as like you said should have been C12 for Time), I've also included the line before as the code is removing the "#N/A" rows but for some reason isn't able to distinguish the dates and time still? Do you have any other suggestions?&nbsp;lr = Cells(Rows.Count, "C").End(xlUp).Row 'find last rowFor i = lr To 2 Step -1 'loop thru backwards, finish at 2 for headersIf Cells(i, "C").Text = "#N/A" Then Rows(i).EntireRow.DeleteNext ilrU = Cells(Rows.Count, "U").End(xlUp).Row 'find last rowDate1 = Worksheets("Reference Sheet").Cells(16, 3).Value2Time1 = Worksheets("Reference Sheet").Cells(12, 3).Value2Date2 = Worksheets("Reference Sheet").Cells(15, 3).Value2For i = lrU To 2 Step -1 'loop thru backwards, finish at 2 for headers'column "U" is column21If (((Cells(i, 21).Value &lt; Date2) And (Cells(i, 22).Value &lt; Time1)) Or _((Cells(i, 21).Value &gt; Date1) And (Cells(i, 22).Value &gt; Time1))) ThenRows(i).EntireRow.Delete

mtarler · Answer

you didn't answer my first question:you say "date is after the date ... and after a certain time ..." so are you comparing DATE/DAY in once case and TIME in the other? If so you have to look at the whole number value for the DATE comparison and the fractional portion for the TIME.basically what are the values in columns U &amp; V and the values assigned to Date1, Date2, and Time1?It would be much easier if you could attach the sheet or give a link to it (no confidential/private/personal info)

clh_1496 · Answer

mtarler&nbsp;&nbsp;I've attached a file which basically has everything blanked out except for the date and time columns and then the reference sheet that is used - hope this is okay. Column U is a date, Column V is a time (they were a combined column but get split into date and time earlier in the macro) and so for Column U I want to delete dates that are before C15 on the reference page (used as Date 2) BUT only those that also have a time in Column V that is earlier than 6.20am (shown in C12 on the reference page and used as Time 1). And then I also want to delete any dates that are after C16 on the reference page or shown as Date 1, but only those that also have a time in Column V that is later than&nbsp;6.20am (shown in C12 on the reference page and used as Time 1).&nbsp;

mtarler · Answer

so in your file the Date cells have #Ref! errors so I had to put in dates.the file you attached also didn't have the macro so I cut and paste from your post above.I also had to add end if and next i statementsbut then it seemed to run fine.The logic you are doing is odd to me but that is fine if it what you want/need.What do you mean by "isn't able to distinguish the dates and time"? Does it give an error or the results aren't what you expect?

Forum Discussion

Macro that deletes rows based on date and time

Share

Resources