VBA to filter (between two dates and times) based on one cell value

[Durtug]

Hi guys

I have a WorkSheet where I have the following info (A1 showing 29.09.2015 when formated as short date):
[TABLE="class: grid, width: 800, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Value(Left(E2;5))[/TD]
[TD]WebID[/TD]
[TD]ID[/TD]
[TD]Agent[/TD]
[TD]StartTime[/TD]
[TD]Other[/TD]
[TD]Other2[/TD]
[TD]Message[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Test[/TD]
[TD]Test[/TD]
[TD]Test[/TD]
[TD]29.09.2015 08:40[/TD]
[TD]Test[/TD]
[TD]Test[/TD]
[TD]Some text[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Test[/TD]
[TD]Test[/TD]
[TD]Test[/TD]
[TD]29.09.2015 15:40[/TD]
[TD]Test[/TD]
[TD]Test[/TD]
[TD]Some other text[/TD]
[/TR]
</tbody>[/TABLE]







The macro I am using, that not work properly is:

Sub Test()

Dim dDate As Date

Set rDate = Sheets("Test").Range("A1") 'Cell housing date & time



If Not IsDate(rDate) Then 'Check if valid

MsgBox "Wrong format in A1"

Exit Sub

End If



dDate = DateSerial(Year(rDate), Month(rDate), Day(rDate))



ActiveSheet.Range("$A$1:$I$100").AutoFilter Field:=5, Criteria1:= _
">=" & dDate & " 08:00:00", Operator:=xlAnd, Criteria2:="<=" & dDate & " 15:00:00"

End Sub




My problem is that this WorkBook will be updated on regular basis (and the date/time will be different each time).
I try to use a macro, and it do not give me an error, or in other ways look like it is not working.
But when I use it, all rows are filtered, and only the heading is showing.
When I the go manually to the filter option on E to look, it look like it are having a "Datefilter" activated.
I then go to the next level, and it's marked with "between", and push that to look.
A messages box is then opened, and showing the date/time I like to filter between.
Because it look right, I push "OK"

Suddenly the filter is working right, and show the rows I want

So the help I need is to understand why my macro seems tho work fine, but filter away all rows and not just the ones between my date/time.
And why it show the right filter options when I look manually, but first work properly when I hit "Ok"

Thanks to the community in advance

 

[Durtug]

Please let me know if any information is missing, that you need to help me.
I will then try (the best I can) to provide the missing information

 

[Durtug]

I would like to check again. (I'm afraid it happens to much on the forum, so a lot of posts gets pushed to side 2 or 3 really fast).

Does anybody have a good solution for me?
I still have the same problem.

 

[John_w]

With non-USA date formats, you have to use Long and Double data types in AutoFilter date criteria. See Excel AutoFilters in VBA Using Dates. Filter by Dates in Excel VBA

This code displays 1 filtered row of your sample data:

Sub Test()

    Dim dDate As Date, rDate As Range
    
    Set rDate = ActiveSheet.Range("A1") 'Cell housing date & time
    
    If Not IsDate(rDate) Then 'Check if valid
        MsgBox "Wrong format in A1"
        Exit Sub
    End If
    
    dDate = DateSerial(Year(rDate), Month(rDate), Day(rDate))
    
    ActiveSheet.Range("$A$1:$I$100").AutoFilter Field:=5, _
        Criteria1:=">=" & CDbl(dDate + TimeValue("08:00:00")), Operator:=xlAnd, Criteria2:="<=" & CDbl(dDate + TimeValue("15:00:00"))

End Sub

 

[Durtug]

Thanks for your help John_w.

But it did not work as expected.
When I now use this macro, it filters on date between 422353333333 and 42235625 (19.08.2015)
and all rows are filtered away.

Do you have any other suggestion for me?

Kind regards, Tommy

 

[John_w]

The date 19.08.2015 with your sample data would filter out all rows, so that result isn't unexpected.

Are the dates/times in column E Excel dates or text strings? The code should work if they are Excel date/times (formatted as required).

 

[Durtug]

It's right it would filter away all rows if the sample data and date 19.08.2015 is used.
But in the real document my date/time changes, and I did not convert the numbers to match sample data.
Maybe I should have done that.
The date/time i column E seems to be dates and times due to the right filtering when done manually.

Does it have anything to do with the formula in A1? It extracts the date from column E, where it also contains time.

If you have the time, and want to, I can send the workbook to you tomorrow.
The you can what I have done or don't have done.

I appreciate all your help so far.

 

[John_w]

What is the formula in A1?

 

[Durtug]

The same as in the sample text above in my original post.

 

[John_w]

The formula =Value(Left(E2;5)) doesn't make sense if E2 contains an Excel date. Try instead =INT(E2).