Help with VBA to delete anything lsss the 30mins

[pkam2010]

HI

i am very new to VBA, I have been working on them but mainly finding them on the net and then pasting and adjusting them to suit. i require help to work out how to delete a row if in column N the time is less than 30mins. Also to note when I paste the data into the excel sheet it is in text format. I have tried a number of codes found on the net and converted the cells to time but doesn’t seem to work.

please help

 

[Joe4]

Welcome to the Board!

Can you post some examples of what these times values being copied down actually look like?

 

[pkam2010]

HI

see below

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl67, width: 64"]Duration[/TD]
[/TR]
[TR]
[TD="class: xl65"]00:54:34[/TD]
[/TR]
[TR]
[TD="class: xl65"]00:01:33[/TD]
[/TR]
[TR]
[TD="class: xl65"]00:09:49[/TD]
[/TR]
[TR]
[TD="class: xl65"]01:20:37[/TD]
[/TR]
[TR]
[TD="class: xl67"]00:02:57[/TD]
[/TR]
[TR]
[TD="class: xl65"]00:03:22[/TD]
[/TR]
[TR]
[TD="class: xl65"]01:26:18[/TD]
[/TR]
[TR]
[TD="class: xl67"]01:39:14[/TD]
[/TR]
[TR]
[TD="class: xl65"]00:00:58[/TD]
[/TR]
[TR]
[TD="class: xl67"]00:04:37[/TD]
[/TR]
[TR]
[TD="class: xl67"]00:03:36[/TD]
[/TR]
[TR]
[TD="class: xl65"]00:07:57[/TD]
[/TR]
[TR]
[TD="class: xl65"]02:28:45[/TD]
[/TR]
</tbody>[/TABLE]

so i need to be able to delete all the rows less then 30mins.

thanks

 

[Joe4]

Try this:

Sub MyDeleteRows()

    Dim c As String
    Dim lr As Long
    Dim r As Long
    Dim dt As Date
    
    Application.ScreenUpdating = False
    
'   Designate which column time values are in
    c = "A"
    
'   Find last row in column with data
    lr = Cells(Rows.Count, c).End(xlUp).Row
    
'   Loop through all rows, starting at the bottom, up to row 2
    For r = lr To 2 Step -1
'       Convert time entry to date/time with current date
        dt = Date + TimeValue(Cells(r, c))
'       Check to see if entry is less than 30 minutes ago
        If (Now() - dt) < (1 / 48) Then
'           Delete row
            Rows(r).Delete
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[pkam2010]

HI

i have tired it doesn't work. i change "A" to "N" as the times are in that column.

 

[pkam2010]

i tired inputting all the data again then running the marco and it is coming with a error of Run-time error '13' type mismatch

 

[Joe4]

It works for me, assuming that your values are really Text.
Note also that all the times in the sample that you posted are over 30 minutes (at least in my time zone), so no rows would be deleted in that example.

If you are copying the data down from the Web, often times other invisible "special" characters (like non-breaking spaces) are copied down, that may be causing the time conversion to fail.
Try adding a message box, and see if it is building the date/time piece conversion successfully. It should return today's date with the time value from the cell.

Sub MyDeleteRows()

    Dim c As String
    Dim lr As Long
    Dim r As Long
    Dim dt As Date
    
    Application.ScreenUpdating = False
    
'   Designate which column time values are in
    c = "A"
    
'   Find last row in column with data
    lr = Cells(Rows.Count, c).End(xlUp).Row
    
'   Loop through all rows, starting at the bottom, up to row 2
    For r = lr To 2 Step -1
'       Convert time entry to date/time with current date
        dt = Date + TimeValue(Cells(r, c))
[COLOR=#ff0000]        MsgBox "Value in cell " & Cells(r, c).Address(0, 0) & ": " & Format(dt, "mm/dd/yyyy hh:mm:ss")[/COLOR]
'       Check to see if entry is less than 30 minutes ago
        If (Now() - dt) < (1 / 48) Then
'           Delete row
            Rows(r).Delete
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[pkam2010]

HI
the time is 00:00:00 is Hrs:min:Sec and not day:Hrs:mm that where may be going wrong

 

[Joe4]

the time is 00:00:00 is Hrs:min:Sec and not day:Hrs:mm that where may be going wrong

.
Yep, I think it may be the extra character.
Is your first time entry in cell N2?
Place this formula in any blank cell, and tell me what it returns?

=LEN(N2)

Then tell me what this formula returns:

=LEFT(N2,8)

 

[pkam2010]

Hi

1st times are in n5 so
=Len(n5) result was 8
=Left(n5) result was 00:54:34
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
</body>