How to filter dates in Excel vba

Question

Struggling with something that should be simple! I am trying to filter a sheet containing dates to show only one date, a variable created from an input box. Whatever I try, and however I define the variable, it results in nothing...

Here is my code:

Sub Filter()
    
Dim TheDate As Double ' I have tried Long, Date, Integer....
Dim rRange As Range    
     
 Set wb2 = Workbooks("JADATA.xlsm") 'To set the workbook
    
 wb2.Sheets("Employee Hours").Activate
 
 If Sheets("Employee Hours").FilterMode = True Then
    Sheets("Employee Hours").ShowAllData
End If
 
TheDate = 44888 'this is the date, 23/11/2022, to test
 
    With Worksheets("Employee Hours")
        Set rTemp = .Range(.Cells(1, 1), .Cells(.Rows.Count, 7).End(xlUp))
    End With
    
    With rTemp
        .AutoFilter field:=1, _
        Criteria1:="=" & CDbl(TheDate)    'Tried without "=" &; and without CDbl...
    End With

End Sub

Please add sample data (as text that can be copy/pasted) that demonstrates the problem. — Ron Rosenfeld
– Ron Rosenfeld, Commented Jan 1, 2023 at 2:06

VBasic2008 · Accepted Answer · 2023-02-01 21:29:41Z

1

Filter By Date

AutoFilter prefers strings. The following should work with your system date separators.

Option Explicit

Sub FilterByDate()
    
    Dim InputDate As String
    InputDate = InputBox("Enter a date:", "Filter by Date")
    
    If Len(InputDate) = 0 Then Exit Sub
    If Not IsDate(InputDate) Then Exit Sub
    
    Dim wb As Workbook: Set wb = Workbooks("JADATA.xlsm")
    Dim ws As Worksheet: Set ws = wb.Sheets("Employee Hours")
    If ws.AutoFilterMode Then ws.AutoFilterMode = False
 
    Dim rg As Range
    Set rg = ws.Range("A1", ws.Cells(ws.Rows.Count, "G").End(xlUp))
    
    rg.AutoFilter Field:=1, Criteria1:=InputDate

    MsgBox "Data filtered by """ & InputDate & """.", vbInformation

End Sub

edited Feb 1, 2023 at 21:29

answered Dec 31, 2022 at 23:28

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to filter dates in Excel vba

1 Answer 1

Filter By Date

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Filter By Date

Comments

Your Answer

Sign up or log in

Post as a guest

Related