CODEWITHSUNDEEP
excelvba
FotoDJ
FotoDJ

Reputation: 351

VBA code to filter sheet by exact date using InputBox

We are trying to get this code working to filter sheet by exact date but still can't figure it out, any ideas what is wrong? Date is formatted in sheet as mm/dd/yyyy

Sub FilterDate()
    Dim ap, dt As Date, dt1 As Date, lr&: lr = ActiveSheet.UsedRange.SpecialCells(11).Row

    With ActiveSheet.[a1].CurrentRegion.Resize(lr, 10)
        ap = Application.InputBox("get date")
        dt = CDate(ap)
       .AutoFilter 10, "=" & dt
    End With
End Sub

File sample

Thank you for pointing out that declaration problem, I did changes in excel tools but code seems to have another issue,any date input leaves only top row...

Upvotes: 2

Views: 789

Answers (1)

Wizhi
Wizhi

Reputation: 6549

You are missing to declaring variables!! Using Option Explicit will make your life much easier since you can't run code without declared variables.

In the VBA editor go to: "Tools" -> "Options" -> "Require Variable Declaration".

Sub FilterDate()
    Dim ap As Date, dt As Date, dt1 As Date
    Dim lr As Long
    lr = ActiveSheet.UsedRange.SpecialCells(11).Row
    With ActiveSheet.[a1].CurrentRegion.Resize(lr, 10)
        ap = Application.InputBox("get date")
        dt = CDate(ap)
        'dt = CDate("2018-10-26")
        .AutoFilter 10, Criteria1:=">=" & dt, _
                     Operator:=xlAnd, Criteria2:="<" & dt + 1
    End With
End Sub

enter image description here

EDIT:

Yes, since you have date you need to filter in an interval, rather than one "=" criteria (two criteria where your date is between those two criteria), don't forget to make a header since the header values always is present (Code is updated). Example below is with date 2018-10-26.

enter image description here

Upvotes: 2

Related Questions