I'm having trouble when filtering date and time values together using VBA on MS Excel. When I need to filter only dates, I find no problem using the code below:
myRange.AutoFilter Field:=3, Criteria1:= _
">=" & CDbl(CDate((Date - 2))), Operator:=xlAnd, Criteria2:="<=" & CDbl(CDate((Date)))
The question is: how could I specify a time when filtering? Example: I'd like to filter results from yesterday at 19:00 until today at 8:00.
I found out a way:
Sub DateTimeFilter()
Dim d1, d2, m1, m2, y1, y2 As Integer
Dim dt1, dt2 As String
d1 = Day(Date - 1)
d2 = Day(Date)
m1 = Month(Date - 1)
m2 = Month(Date)
y1 = Year(Date - 1)
y2 = Year(Date)
dt1 = m1 & "/" & d1 & "/" & y1
dt2 = m2 & "/" & d2 & "/" & y2
ActiveSheet.Range("$A$3:$L$2012").AutoFilter Field:=3, Criteria1:= _
">=" & dt1 & " 19:00", Operator:=xlAnd, Criteria2:="<=" & dt2 & " 08:00"
End Sub
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments