delete entire row with condition (simple but failed)

[auzanoo]

dear expert,

i have a data and code, but it's failed.

from this:

GOPI	09:29-12:00
TOPIK TERKINI	10:26-10:27
TOPIK TERKINI	11:45-11:47
BEPANNAH(R)	12:00-14:00

should be this:

GOPI	09:29-12:00
BEPANNAH(R)	12:00-14:00

so, the time should continue from one to another one.

but, the result is like this:

GOPI	09:29-12:00
TOPIK TERKINI	11:45-11:47

this is my code,

Sub DeleteRowswithSpecificValue()

Blank = Range("A1", Range("A1").End(xlDown)).Rows.Count

For i = 1 To Blank
If Right(Cells(i, 2),5) <> Left(Cells(i+1, 2),5) Then
Cells(i+1, 2).EntireRow.Delete

End If
Next i
End Sub

and i want to add exceptional:
if the time is not so far (around 1 or 2 minutes), then dont delete the row.

for example,
from this:

GOPI	09:29-12:00
TOPIK TERKINI	10:26-10:27
TOPIK TERKINI	11:45-11:47
BEPANNAH(R)	12:02-14:00

to this:

GOPI	09:29-12:00
BEPANNAH(R)	12:02-14:00

i dont know what to do with this one.

thanks in advance!

 

[Zot]

Something like this
T = Abs(DateDiff("n", Right(Replace(Cells(i, 2), ".", ":"), 5), Left(Replace(Cells(i + 1, 2), ".", ":"), 5)))

 

[auzanoo]

Something like this
T = Abs(DateDiff("n", Right(Replace(Cells(i, 2), ".", ":"), 5), Left(Replace(Cells(i + 1, 2), ".", ":"), 5)))

sorryyy but, that's not the problem.

i mean, the code works for 00:00 to 24:00 format,
but my data format is 02:00 to 26:00.

i think maybe it has to be subtract with 02:00 (2 hours) first, then do the calculation.

i tried with

 - TimeSerial (2,0,0)

and something else, but its not working

 

[Zot]

sorryyy but, that's not the problem.

i mean, the code works for 00:00 to 24:00 format,
but my data format is 02:00 to 26:00.

i think maybe it has to be subtract with 02:00 (2 hours) first, then do the calculation.

i tried with

 - TimeSerial (2,0,0)

and something else, but its not working

What is 26:00? Just keep adding time from starting time?

 

[Jon von der Heyden]

I'm not going to get stuck into the date problem, but just want to call out that you may want to pay attention to Mark's suggestion to test this where 3 consecutive rows meet the delete condition, and to heed his advice to loop backwards rather than forwards.

 

[auzanoo]

What is 26:00? Just keep adding time from starting time?

soo, the actual data is like this

A1	02:00-02:09
A2	02:09-02:30
A3	02:30-03:45
A4	03:45-04:30
A5	04:30-04:59
A6	04:59-06:22
A7	06:22-08:00
A8	08:00-09:17
A9	09:17-09:49
A10	09:49-10:49
A11	10:49-12:21
A12	11:54-11:56
A13	12:21-14:54
A14	14:19-14:20
A15	14:54-16:26
A16	16:26-18:29
A17	18:29-20:29
A18	20:29-23:11
A19	23:11-24:38
A20	24:38-25:39
A21	25:09-25:39
A22	25:39-25:59

it is start from 02:00, and end with 25:59.

I'm not going to get stuck into the date problem, but just want to call out that you may want to pay attention to Mark's suggestion to test this where 3 consecutive rows meet the delete condition, and to heed his advice to loop backwards rather than forwards.

nooo there is no other problem except the date

 

[Zot]

soo, the actual data is like this

A1	02:00-02:09
A2	02:09-02:30
A3	02:30-03:45
A4	03:45-04:30
A5	04:30-04:59
A6	04:59-06:22
A7	06:22-08:00
A8	08:00-09:17
A9	09:17-09:49
A10	09:49-10:49
A11	10:49-12:21
A12	11:54-11:56
A13	12:21-14:54
A14	14:19-14:20
A15	14:54-16:26
A16	16:26-18:29
A17	18:29-20:29
A18	20:29-23:11
A19	23:11-24:38
A20	24:38-25:39
A21	25:09-25:39
A22	25:39-25:59

it is start from 02:00, and end with 25:59.



nooo there is no other problem except the date

Looks like minus 2 to offset the hour like you thought is the solution since it will not affect minute.

 

[Zot]

I'm not going to get stuck into the date problem, but just want to call out that you may want to pay attention to Mark's suggestion to test this where 3 consecutive rows meet the delete condition, and to heed his advice to loop backwards rather than forwards.

I think this is something like comparing difference progressively to the next one ignoring the deleted data. I have no idea on the purpose ?