Delete Rows Based on Time criteria

**Roelandu** · 01-30-2012, 05:58 AM

Hi,

I'm trying to solve a problem i have with my macro code for deleting entire rows when cell values in column V
are between 23:00:00 and 08:00:00 o'clock. The cells in column V are formatted as 'time' and the number of rows in the sheet
must be able to change.

The code worked fine for another
similar sheet with time criteria between 19:00:00 and 08:00:00. I can't seem to figure out what the problem is here..

The code i have right now is:

Sub Delete_rows

Sheets("New").Select

For i = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(i, "V") > "23:00:00" And Cells(i, "V") < "8:00:00" Then
ActiveSheet.Cells(i, "A").EntireRow.Delete
Cells(i - 1, 1).Select
End If
Next i

End Sub

Can anyone help me with this one?

Thanks a lot!

**Domski** · 01-30-2012, 06:19 AM

Try this:

Sub Delete_rows()
    Dim i As Long
    With Sheets("New")
        For i = .Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
            If .Cells(i, "V") > TimeSerial(23, 0, 0) Or Cells(i, "V") < TimeSerial(8, 0, 0) Then
                .Rows(i).Delete
            End If
        Next i
    End With
End Sub

Dom

**Roelandu** · 01-30-2012, 08:38 AM

Thanks Dom. When i apply this code, it deletes everything but row 1 (the header). I think the OR operator
is not the best one anyway because both conditions (>23h and <8h) need to be fulfilled, so i'd rather go for AND even
though that doesn't work either.

I'm quite desperate, i've been trying stuff for hours but i can seem to make it work

I'm definitly not an expert in this kind of codes.

Any help is welcome!

**Domski** · 01-30-2012, 08:57 AM

A single time value can't really be greater than 23:00 and less the 08:00 so Or would be the operator I think. Can you upload a sample file?

Dom

**Roelandu** · 01-30-2012, 09:43 AM

Yes you're right! It should be Or. I figured it out. My time format was "1:00:00" for 1-digit hours. I changed it to "01:00:00" and now your code works fine.
I didn't use the TimeSerial(), it also works with the time between "".

The code:

Sub DeleteRows()

Dim i As Long
With Sheets("New")
For i = .Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If .Cells(i, "V") > "23:00:00" Or .Cells(i, "V") < "08:00:00" Then
.Rows(i).Delete
End If
Next i
End With
End Sub

Thanks a lot man! You saved me