Deleting Cells Based on time

[jdavis2506]

Hello,

Currently i run a weekly report that drags in the past weeks breakdowns and i am trying to find a way to delete a whole row of cells if the time is before 8AM.

I have had a number of attempts but i cant get anything to work;

Sub DeleteRows()
Dim i As Long
With Sheets("12 hr Nights")
' I want to ignore the first 7 rows
For i = .Cells(Rows.Count, "J").End(xlUp).Row To 7 Step -1
If .Cells(i, "J") < "08:00:00" Then
.Rows(i).Delete
End If
Next i
End With
End Sub

I think the problem im having is the date is in the same column as the time (01/03/2016 06:45:00). Im new to vba so any help would be appreciated.

Thanks!

 

[steve the fish]

Hi. You could try this:

With Sheets("12 hr Nights")
     ' I want to ignore the first 7 rows
    For i = .Cells(Rows.Count, "J").End(xlUp).Row To 8 Step -1 'To 8 if want to ignore 7 rows
        If .Cells(i, "J") - Int(.Cells(i, "J")) < 8 / 24 Then .Rows(i).Delete
    Next i
End With

If you have large dataset then you could consider an autofilter method instead of a loop but this works.

 

[jdavis2506]

Thanks for the reply!

Sorry it took me so long to get back, the code worked perfectly however like you said it is a large dataset about 30,000 lines.

would the autofilter be hard to code or is it relatively simple code as well?

Thanks again!