Trying to delete row based on value in cell

[HulkSmash]

hi,

I'm trying to delete rows where the time in ColumnC is between 18:30 and 08:55, not sure whats wrong with my coding but when I run it it removes all data and only leaves the header?

Please Login or Register  to view this content.

[AB33]

should not be and

If .Cells(idel, "C") > "18:30:00" and.Cells(idel, "C") < "08:55:00" Then

[HulkSmash]

Well spotted however that still doesnt delete the rows

[AB33]

Most likely reason would be excel does not recognise the time format. You can record a filter function and see how excel picks up the relevant range.

[HulkSmash]

hi,

i have already set the format of the column to hh:mm:ss - its driving me mad

[AB33]

Could you attach the sample which is driving you mad?

[HulkSmash]

please find it attached, hopefully you can spot something which i have missed

[AB33]

If .Cells(idel, "C") > "17:35:00" And .Cells(idel, "C") < "08:55:00" Then

Unless 08 is the next day, it can not be less than 17.35.

For e.g 19:15:01
which is the last row is greater than 17:35:00, but it can not be less than 08.55.
If I were to convert the time in to digital, I get the following
0.371539352: for 08:55:00

0.802094907:19:15:01

0.802094907 can be less than 0.371539352

[AB33]

The first OR statement you had works, but I do not think it is right if you are looking between the two times.

[HulkSmash]

ok thanks for the explanation, do you think it would be possible to achieve my goal of being able to delete all rows if the time is between 17:35 and 08:55?

Just for clarification i did try the following but it again it did not delete anything.

If .Cells(idel, "C") > "17:35:00" Then

[AB33]

See if this works for you.

[HulkSmash]

hi, that didnt work for me? it didnt error or remove the rows.

[AB33]

I put the code on the module and have already run it. You can copy the original data (Sheet2) in to sheet1 and run it. I ended up with 6 rows.