Need VBA code to delete all rows except range, struggling for hours

[igurman]

Hi, so I have a financial data range with lots of rows and 7 columns. Row B has timestamp (03:14, 03:15, etc).

I need to ask the user what time he wants to choose, then user inputs 14:00 for example, and I need to delete all rows from the sheet, except rows starting at 14:00 and finishes at 15:00 (so 1 hour after user inputed time).

- There may be seconds on some rows (so 15:03:04, and 15:03:45), so it's not exactly 60 rows
- Maybe I should ask the user to input the time 1 hour after the first input time (so ask to input 14:00 and then 15:00 for our example)?

In the end, I need to search in one column (B) the time he has input, and delete everything except all rows during 1 hour from that time, so before 14:00 and after 15:00 should be deleted..

Any chance someone can help me?

Thanks
Ilya

 

[mikerickson]

Perhaps

Sub test()
    Dim strUITime As String, uiTime As Date
    Dim strPrompt As String
    Dim isGood As Boolean
    Dim rngData As Range, i As Long
    
    Rem adjust the data Range
    With Sheet1.Range("B:B")
        Set rngData = Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
    End With
    
    strPrompt = "Enter a time in h:mm format"
    
    Do
        isGood = False
        strUITime = Application.InputBox(strPrompt, Type:=2)
        If strUITime = "False" Then Exit Sub: Rem user canceled
    
        If IsDate(strUITime) Then
            uiTime = TimeValue(strUITime)
            If uiTime = 0 Then
                MsgBox "That was a date, not a time. Try again"
            Else
                Rem user entered time
                isGood = True
            End If
        Else
            MsgBox "That is not a time. Try again"
        End If
    Loop Until isGood
    
    With rngData
        For i = .Rows.Count To 1 Step -1
            If Hour(.Cells(i, 1).Value) = Hour(uiTime) Then
                Rem keep
            Else
                .Cells(i, 1).EntireRow.Resize(1, 7).Delete
            End If
        Next i
    End With
End Sub

 

[JLGWhiz]

this does not delete the other rows, but hides them by filtering.

Sub t()
Dim tm As Date
tm = Application.InputBox("Enter the start time, eg. 12:00:01 PM", "TIME", Type:=1)
    If tm = False Then Exit Sub
   
Sheet1.UsedRange.AutoFilter 2, ">=" & tm, xlAnd, "<" & DateAdd("h", 1, tm)
'Sheet1.AutoFilterMode = False
End Sub

 

[igurman]

Both of your suggestions work perfectly, thank you! Additional question since I get sometimes slightly different formats:

- Sometimes unfortunately I don't get separated columns A for date and B for time, but get one column with row content like that: "13.03.2019 13:36:01.126 GMT+0100" or just "13.03.2019 13:36:01", is there any way to make an adjustment that can deal with that? Seems more tricky..

Thanks again!

 

[JLGWhiz]

So long as they translate to a a date data type, the code should still work because the value for the time is actually an integer double data type. Excel just displays the dates and times in formats like 1/1/2019 and 12:05:00 PM. Where the date is actually 43466 value and the time is read in decimal values. So how it looks to us is not how it is seen by Excel.