Delete Rows Based on Time criteria

[Roelandu]

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]

Try this:

Please Login or Register  to view this content.

Dom

[Roelandu]

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]

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]

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

[rvkadu1]

Can it be applicable if column V is date & time (3/9/2012 12:00:00 AM), i want to delete rows based on time only date may be different / any one

RK

[arlu1201]

rvkadu1,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.