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]

Looks like you want to compare the rest of the line with row 1 only. So, row 1 is a fix reference. Then you'll get the result you're looking for.

Ops.... don't really understand on the time difference.

 

[MARK858]

For i = 1 To Blank

You should loop from bottom to top when deleting as the rows shift up, try

For i = Blank to 1

 

[GWteB]

You should loop from bottom to top when deleting as the rows shift up, try

For i = Blank to 1

For i = Blank to 1 Step -1

would be better ...

 

[MARK858]

For i = Blank to 1 Step -1

would be better ...

Teach me for typing on my phone

 

[auzanoo]

still doesnt work guys

 

[Zot]

I guess you want something like this?

Sub DeleteRowswithSpecificValue()

Dim Blank As Long, T As Long

Blank = Range("A1", Range("A1").End(xlDown)).Rows.Count
For i = 1 To Blank
    If Not IsEmpty(Cells(i + 1, 2)) Then
        T = Abs(DateDiff("n", Right(Cells(i, 2), 5), Left(Cells(i + 1, 2), 5)))
        If T > 2 Then
            Cells(i + 1, 2).EntireRow.Delete
            i = i - 1
        End If
    End If
Next i

End Sub

 

[auzanoo]

I guess you want something like this?

Sub DeleteRowswithSpecificValue()

Dim Blank As Long, T As Long

Blank = Range("A1", Range("A1").End(xlDown)).Rows.Count
For i = 1 To Blank
    If Not IsEmpty(Cells(i + 1, 2)) Then
        T = Abs(DateDiff("n", Right(Cells(i, 2), 5), Left(Cells(i + 1, 2), 5)))
        If T > 2 Then
            Cells(i + 1, 2).EntireRow.Delete
            i = i - 1
        End If
    End If
Next i

End Sub

Yes! this is perfectly works! thank you so much.

but i got a problem...
my time format is 02.00-25.59
and the macro doesnt work at 24.00-25.59.
could you handle this? thank you

 

[Zot]

Yes! this is perfectly works! thank you so much.

but i got a problem...
my time format is 02.00-25.59
and the macro doesnt work at 24.00-25.59.
could you handle this? thank you

You can use Replace function to replace dot to double dot in before doing calculation.

 

[MARK858]

Yes! this is perfectly works! thank you so much.

Put 3 rows together that need deleting, does the code delete all of them?