VBA delete rows if time is more than 9:30:00

[cazdealer]

Hi,

I have this sort of Data from row 1 to row 9999


A B C
200 22.95 09:28:47 AM
300 56.67 09:29:32 AM
200 12.75 09:30:06 AM
100 75.97 09:32:47 AM
200 22.95 09:49:00 AM
etc...

I want to find a way the create a VBA procedure that would delete all the rows that the time in column C is over 09:30:00 AM

I have tried to convert the time into a number and then if formula to see if the number is higher than 0.3958333 but it doesn't work

any other ideas?

 

[buxtongt]

Can you filter the 3 columns and do a "greater than" criteria on it? You could then show everything but that and select all visible rows and delete them. Hope this helps.

 

[Jonmo1]

Are your times in column C time only?
Or are they Date AND Time?

Format column C as Number, with several decimal places.
Are they showing as
0.xxxxx

or do they have whole numbers as well like:
40875.xxxxx

 

[cazdealer]

yes, they are time only in column C (not date and time)

when I convert them, they become something like 0.395625 or 0.395636574074074

 

[Jonmo1]

Try

Sub Foo()
Dim i As Long, lr As Long
 
lr = Cells(Rows.Count, "C").End(xlUp).Row
 
For i = lr To 1 Step -1
    With Cells(i, "C")
        If IsNumeric(.Value) And .Value > TimeSerial(9, 30, 0) Then Rows(i).EntireRow.Delete
    End With
Next i
End Sub

 

[bill shockley]

Sub tester()
lastRow = Cells(1).End(xlDown).Row
s1 = TimeValue("9:30 am")
For i = lastRow To 1 Step -1
s = Cells(i, 3)
If s > s1 Then Rows(i).Delete
Next i
End Sub

going backwards saves having to renumber i
everytime a row is deleted.

 

[cazdealer]

Both procedures works perfectly!

thank you very much!