M

[meg56]

I'm an inexperienced Excel user and I have a very large set of data with the first column being the time of each entry. I know that to filter out a specific time range, I can use the Filter function. However, I have a large number of different time ranges to filter out. I would like to write a macro that can be adjusted to filter for different time ranges. For example, I want to find the data from 10:00:00 to 10:30:00, and then find the data from 2:00:00 to 2:30:00. Is it possible to do this on Excel? Would I be able to accomplish this in VBA? Currently, I only know how to write a macro that filters one specific time range. Any help is appreciated. Thanks for your time.

 

[XLfriend777]

Hi meg56 - Welcome to the forum! I'm sure that what you want to do is possible. It may take a few iterations to get the date/time formats correct, but I know it can be done. You might try the code below and see if this helps us get started. Let us know how it goes. Good luck.

Sub meg56_SelectTime()
StartTime = InputBox("Enter Start Time")
EndTime = InputBox("Enter End Time")
'    ActiveSheet.Range("$A$1:$A$1000").AutoFilter Field:=1, Criteria1:=">=" & StartTime, _
'        Operator:=xlAnd, Criteria2:="<=" & EndTime
    ActiveSheet.Range("$A:$A").AutoFilter Field:=1, Criteria1:=">=" & StartTime, _
        Operator:=xlAnd, Criteria2:="<=" & EndTime
End Sub

 

[meg56]

Hi goesr, thank you for the suggestion. I tried out this code, and it allowed me to input the start and end times I wanted. However, when I applied the filter, all of the data disappeared, even the data that fit my criteria. I made sure the range included all of my cells as well, so I'm not sure why this happened. Any advice would be appreciated- thanks so much.

 

[meg56]

Update: I played with the time formats and the code works. Thanks very much for the help.

 

[XLfriend777]

Hi meg56 - GREAT. I'm glad you were able to get it to work. Those date/time formats can be tricky, but once you get the right format it should work. Thanks for the feedback. Often we don't hear if our suggestions work or not. Have a great long weekend!