Forum Discussion
Macro that deletes rows based on date and time
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 th...
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 statements
but 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?
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 statements
but 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?
I would imagine the logic order is off because I've combined 6 or 7 separate macros into one, and my coding skills are pretty basic.
Where have you added the end if and next i statements?
No error comes up, just nothing happens - as in it doesn't seem to recognise the dates that are before or after the dates in the macro need deleting so they remain
clh_1496 Here is the macro I created from your post:
Public Sub removeRows() 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 lrU = Cells(Rows.Count, "U").End(xlUp).Row 'find last row Date1 = Worksheets("Reference Sheet").Cells(16, 3).Value2 Time1 = Worksheets("Reference Sheet").Cells(12, 3).Value2 Date2 = Worksheets("Reference Sheet").Cells(15, 3).Value2 For i = lrU To 2 Step -1 'loop thru backwards, finish at 2 for headers 'column "U" is column21 If (((Cells(i, 21).Value < Date2) And (Cells(i, 22).Value < Time1)) Or _ ((Cells(i, 21).Value > Date1) And (Cells(i, 22).Value > Time1))) Then Rows(i).EntireRow.Delete End If 'added Next i 'added End Suband in the attached I copied the 'Clockings' sheet to run it on and test it and it seems to delete a bunch of lines