I infer that your times are in 'military' time, i.e. 13:00, 22:00, etc since your format reference does not include an 'AM/PM' indicator.
If so, here is a VBA method to 'filter' by the time range you specify, without regard to the date, simply by activating a keyboard shortcut determined by you.
This is a 'toggle' type event handler: Press Ctrl + your shortcut letter, enter your beginning and ending time parameters, and the data will be filtered to show only times that fall within that range.
Press Ctrl + your shortcut letter again, and all rows will become visible. Repeat as needed.
Copy the following macro to the clipboard:
Sub Custom_Time_Filter()
Dim i, LastRow
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = 1 To LastRow
If Cells(i, "A").EntireRow.Hidden = True Then
Cells.EntireRow.Hidden = False
Exit Sub
End If
Next
here:
sTime = Format(InputBox("Please enter the Starting time, in military format.", _
"Start Time"), ".###")
If sTime = "" Then
Exit Sub
End If
etime = Format(InputBox("Please enter the Ending time, in military format.", _
"End Time"), ".###")
If etime = "" Then
Exit Sub
End If
If Val(sTime) > Val(etime) Then
MsgBox "The Start time must be earlier than the Ending time", vbCritical, ""
GoTo here
End If
For i = 2 To LastRow
If Format(TimeValue(Cells(i, "A")), ".###") < sTime Or _
Format(TimeValue(Cells(i, "A")), ".###") > etime Then
Cells(i, "A").EntireRow.Hidden = True
End If
Next
End Sub
Press ALT + F11
In the menus at the top of the VBE, select INSERT > MODULE
Paste the macro into the editing area to the right.
Close the VBE and return to the worksheet.
Press ALT + F8
When the Macros window opens, highlight the macro and click 'Options...'
Enter a letter to be used as a keyboard shortcut and click 'OK'. The letter can be lower or upper case. Suggest maybe using the letter 't'.
Close the Macros window.
Press Ctrl + your shortcut letter and, in sequence, two inputboxes will appear. Enter the start time, then the ending time. The list will be filtered to show that range only.
Note: enter the times in military format, i.e. 07:00 , 15:00, etc. Also note that the Start time must be earlier than the Ending time.
Now as to using this in any workbook you are working with, you will have to create a Personal.xls workbook to contain the macro and recreate the shortcut in that workbook.
Open a new workbook, and repeat the above process in it to add the macro and create the shortcut in that workbook. Save the workbook and name it 'Personal' (no quotes).
Then either copy that workbook to the xlStart folder in the Microsoft Excel files, or create a new folder on your C drive, place a reference to its original saved path in the Excel options.
For Excel 2003, and prior, go to:
Tools > Options and select the 'General' tab.
In the 'At startup, open all files in' textbox, enter the path to the workbook. Assuming you created a folder named 'Personal' on the root C drive, and copied the Personal.xls workbook to that folder, you would enter:
C:\Personal
============
For Excel 2010:
Go to File > Options and select the Advanced menu item.
Scroll down to 'General' options, near the bottom, and make the same entry in the same textbox as above.
Once you have appropriately completed the above method, when you open any workbook the Personal.xls workbook will automatically be opened. All macros contained in Personal.xls are available to any open workbook, using the keyboard shortcut assigned in Personal.xls.
If you have difficulties, please drop me an email.