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]

Thanks again.
I will try that formula when I am at work tomorrow.