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]

Please try one more:

=ISNUMBER(N5)

 

[pkam2010]

result of isnumber is false

 

[Joe4]

This makes no sense to me.
By returning FALSE, it is saying that it is a Text entry.
By returnng a length of 8, that is telling us that there is no extra invisible characters in there.
So, based on that, the TimeValue function should work, converting that to a time. But it does not appear to be doing that, if it is returning "00:00:00".

Are you sure that you have entries in there that SHOULD be deleted?
I am not sure what time zone you are in, what is your current time there?
Can you provide an example of a value that should be deleted, but is not?
And what version of Excel are you using?

 

[pkam2010]

Hi

Are you sure that you have entries in there that SHOULD be deleted? not sure want you mean. i am getting the date off a report from an excel sheet.
I am not sure what time zone you are in, what is your current time there? UK and its 14:00
Can you provide an example of a value that should be deleted, but is not? 00:01:33 so if it less the 00:30:00 i want it to delete it
And what version of Excel are you using? Excel 2016

 

[Joe4]

OK, I think I totally misunderstood the criteria. I thought you wanted to delete anything with a time within the past 30 minutes (so relative to the current time). whereas I see now that you just want to delete anything less than 00:30:00. This code should do it:

Sub MyDeleteRows()

    Dim c As String
    Dim lr As Long
    Dim r As Long
    Dim tm As Date
    
    Application.ScreenUpdating = False
    
'   Designate which column time values are in
    c = "N"
    
'   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
        tm = TimeValue(Cells(r, c))
'       Check to see if entry is less than 30 minutes
        If tm < (1 / 48) Then
'           Delete row
            Rows(r).Delete
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[pkam2010]

HI

again is coming up with run time error 13
type mismatch

it is highlighting

tm = TimeValue(Cells(r, c))

 

[pkam2010]

HI

it has worked but when it has deleted the lines it comes up with the error in last post

 

[Joe4]

I am guessing that you may have an invalid time value in one of the cells.

Let's add some error handling to your code, and find out what record it is choking on. If you run this, it should tell you the value and cell it doesn't like:

Sub MyDeleteRows()

    Dim c As String
    Dim lr As Long
    Dim r As Long
    Dim tm As Date
    
    Application.ScreenUpdating = False
    On Error GoTo err_chk
    
'   Designate which column time values are in
    c = "N"
    
'   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
        tm = TimeValue(Cells(r, c))
'       Check to see if entry is less than 30 minutes
        If tm < (1 / 48) Then
'           Delete row
            Rows(r).Delete
        End If
    Next r
    
    On Error GoTo 0
    Application.ScreenUpdating = True
    
    Exit Sub
    
'error handling routine
err_chk:
    If Err.Number = 13 Then
        MsgBox "Cannot convert value of " & Cells(r, c) & " in cell " & _
            Cells(r, c).Address(0, 0) & " to a valid time value."
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
    
End Sub

NOTE: That you may need to update the value here 2:

    For r = lr To [B][COLOR=#ff0000]2[/COLOR][/B] Step -1

This should be the first row of your data (where it starts). If it is higher than that and includes text, like a header, it will cause an error.

 

[pkam2010]

Hi thanks that has worked like a charm.

i change the 2 to 5, it was the header in N4 that was bring up the error

 

[Joe4]

You are welcome.

Sorry for the confusion earlier.