Input Date for Filter

[luckyearl]

Hi
I want to filter records that lie in between dates, with startDate & endDate cells F1 & H1, Data is in A2:E2000, Date in just wondered if I could input the dates in a dialog (do away with F1& H1), also make the range dynamic
My Code

Sub MyDateFilter()
    Dim lngStart As Long, lngEnd As Long
    lngStart = Range("F1").Value 'start date
    lngEnd = Range("H1").Value 'end date
    Range("A1:A2000").AutoFilter field:=1, _
        Criteria1:=">=" & lngStart, _
        Operator:=xlAnd, _
        Criteria2:="<=" & lngEnd
End Sub

 

[kevin9999]

I'm not sure what you mean by input the dates in a dialog but, using the existing input method - as long as your data is contiguous, you can use the CurrentRegion method to define your range, which is dynamic by nature. Something like:

Sub MyDateFilter()
    Dim lngStart As Long, lngEnd As Long
    lngStart = Range("F1").Value
    lngEnd = Range("H1").Value

    With ActiveSheet.Cells(1, 1).CurrentRegion
        .AutoFilter 1, ">=" & lngStart, 1, "<=" & lngEnd
    End With
End Sub

If you mean use an input box method to get your dates, then something like the following should do what you want:

Sub MyDateFilter2()
    Dim lngStart As String, lngEnd As String
    lngStart = InputBox("Enter start date")
    lngEnd = InputBox("Enter end date")

    With ActiveSheet.Cells(1, 1).CurrentRegion
        .AutoFilter 1, ">=" & CLng(CDate(lngStart)), 1, "<=" & CLng(CDate(lngEnd))
    End With
End Sub

 

[luckyearl]

Thank you @kevin9999, this is what I was looking. Just wondering if could have a default date in input box

lngStart = InputBox("Insert Date in format dd/mm/yyyy", "Start Date Input box",1 / 10 / 2020)
'returns 4.95049504950495E-05

 

[luckyearl]

Hi, tried again

 lngStart = InputBox("Insert Date in format dd/mm/yyyy", "Start Date Input box", CLng(CDate("01/10/2020")))
'returns 44105, and the filtering works

 

[kevin9999]

lngStart = InputBox("Enter start date", , "1/10/20")

Note the 2 commas

 

[kevin9999]

Hi, tried again

 lngStart = InputBox("Insert Date in format dd/mm/yyyy", "Start Date Input box", CLng(CDate("01/10/2020")))
'returns 44105, and the filtering works

If it works - stick with it

 

[luckyearl]

Thank you @kevin9999
it works, my work flow, just wondering if could combine the both inputBox as one (being greedy here )

Sub MyDateFilter2()
    Dim lngStart As String, lngEnd As String
    lngStart = InputBox("Insert Date in format dd/mm/yyyy", "Start Date Input box", "1/10/20")
    lngEnd = InputBox("Insert Date in format dd/mm/yyyy", "End Date Input box", "31/01/2021")

    With ActiveSheet.Cells(1, 1).CurrentRegion
        .AutoFilter 1, ">=" & CLng(CDate(lngStart)), 1, "<=" & CLng(CDate(lngEnd))
    End With
    Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Index").Select
Range("a12").Select
Selection.PasteSpecial xlPasteValues
End Sub

 

[kevin9999]

Thank you @kevin9999
it works, my work flow, just wondering if could combine the both inputBox as one (being greedy here )

Sub MyDateFilter2()
    Dim lngStart As String, lngEnd As String
    lngStart = InputBox("Insert Date in format dd/mm/yyyy", "Start Date Input box", "1/10/20")
    lngEnd = InputBox("Insert Date in format dd/mm/yyyy", "End Date Input box", "31/01/2021")

    With ActiveSheet.Cells(1, 1).CurrentRegion
        .AutoFilter 1, ">=" & CLng(CDate(lngStart)), 1, "<=" & CLng(CDate(lngEnd))
    End With
    Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Index").Select
Range("a12").Select
Selection.PasteSpecial xlPasteValues
End Sub

Not really. You'd be looking for a UserForm to achieve that, and if it was me, I wouldn't go for the added complexity. There's a lot to be said for simplicity

 

[luckyearl]

I would take your advice and stick with "simple" solution. I don't think Mac version has UserForm. Thank you again

 

[kevin9999]

I would take your advice and stick with "simple" solution. I don't think Mac version has UserForm. Thank you again

You're welcome, and thanks for the feedback.