Use AutoFilter in Excel VBA to Filter by Date & Time
AutoFilter by Date and Time
Got any Excel/VBA Questions? Free Excel Help.
See Also Links Below if Not Familiar With AutoFilter in VBA
AutoFilter by Date & Time With VBA
The 3 example codes below show how we can autofilter by date and time with VBA code.
Sub ByDateTimeGreater()
Dim dDate As Date, dTime As Date
Dim strDate As String
Dim lDate As Long, dblTime As Double
Dim dDateTime As Double
dDate = DateSerial(2006, 9, 12)
lDate = dDate
dTime = TimeSerial(12, 8, 0)
dblTime = dTime
dDateTime = lDate + dblTime
Range("A1").AutoFilter
Range("A1").AutoFilter Field:=1, Criteria1:=">" & dDateTime
End Sub
Sub ByCellDateTimeGreater()
Dim dDate As Date, dTime As Date
Dim strDate As String
Dim lDate As Long, dblTime As Double
Dim dDateTime As Double
Dim rDate As Range, rTime As Range
Set rDate = Sheet1.Range("G1") 'Cell housing date & time
If Not IsDate(rDate) Then 'Check if valid
MsgBox "Non valid date and time in Sheet1 G1)"
Exit Sub
End If
dDate = DateSerial(Year(rDate), Month(rDate), Day(rDate))
lDate = dDate
Set rTime = rDate
dTime = TimeSerial(Hour(rTime), Minute(rTime), Second(rTime))
dblTime = dTime
dDateTime = lDate + dblTime
Range("A1").AutoFilter
Range("A1").AutoFilter Field:=1, Criteria1:=">" & dDateTime
End Sub
Sub ByDateTimeEqual()
Dim dDate As Date, dTime As Date
Dim strDate As String
Dim lDate As Long, dblTime As Double
Dim dDateTime As Double
dDate = DateSerial(2006, 9, 12)
lDate = dDate
dTime = TimeSerial(12, 8, 0)
dblTime = dTime
dDateTime = lDate + dblTime
'= to does not work for non US date systems. So as with dates, we use
'Greater than or Equal to and Less than or Equal to the SAME date & time.
Range("A1").AutoFilter
Range("A1").AutoFilter Field:=1, Criteria1:=">=" & dDateTime, Operator:= _
xlAnd, Criteria2:="<=" & dDateTime
End Sub
See also:
| Use Array Elements To Fill a Range With Headings |
| Automatically Add Date, Time or Date & Time |
| Display Excel AutoFilter Criteria |
| Excel AutoFilters in VBA using Dates |
| Criteria for VBA filters |
| Excel VBA AutoFilters |
Free Training Course: Lesson 1 - Excel Fundamentals
See also: Index to Excel VBA Code; Index to Excel Freebies; Lesson 1 - Excel Fundamentals; Index to how to… providing a range of solutions
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
