Use AutoFilter in Excel VBA to Filter by Date & Time

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL | More Free Downloads.. Best Value: Finance Templates Bundle

AutoFilter by Date and Time

Got any Excel Questions? Free Excel Help .

See Also Links Below if Not Familiar With AutoFilter in VBA

Excel VBA & AutoFilter AutoFilter provides us with a MUCH faster alternative to loops of all kinds
Excel VBA AutoFilter Criteria How to add the criteria for AutoFilter in VBA.
Excel VBA AutoFilter Dates Using dates in AutoFilter can be tricky if not using the US date format.

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

New & Less Than You Think: List Managers | Working With Excel Sheets In VBA | Excel Charting Lessons | Delete rows by condition | TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL | More Free Downloads.. Best Value: Finance Templates Bundle

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft Ž and Microsoft Excel Ž are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

GIVE YOURSELF OR YOUR COMPANY 24/7 MICROSOFT EXCEL SUPPORT & QUESTIONS