Forum Discussion

clh_1496's avatar
clh_1496
Brass Contributor
Jun 15, 2022

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...
excel
Formulas and Functions
Macros and VBA
clh_1496's avatar
clh_1496
Brass Contributor
Jun 28, 2022

mtarler 

 

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

mtarler's avatar
mtarler
Silver Contributor
Jun 28, 2022

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 Sub

and in the attached I copied the 'Clockings' sheet to run it on and test it and it seems to delete a bunch of lines 

Resources