VBA Filter Data to Date

[Andrew_UK]

Hi All,

Part of my code isn't working. I need to filter the data to a date specified by the user.

Sub Pomotions()

Dim Lastrow as Integer
Dim Daterange As Date

lastrow = Columns("A:A").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

Daterange = InputBox("Please enter effective date (DD/MM/YYYY)")

Rows("1:1").Select
Selection.AutoFilter

ActiveSheet.Range("$A$1:$BL" & lastrow).AutoFilter Field:=4, Operator:= _
        xlFilterValues, Criteria1:=Daterange
End Sub

I can see dates in my data which match the exact date range that I input, but still it skips them.

I've built many filters before, but never tried to do this on date filters. Any ideas where I'm going wrong?

I tried recording macros using the filter "Date Filter", but couldn't see any nuances that jumped out at me.

Thanks in advance

 

[Joe4]

I believe that I read that VBA uses US Date formatting.

So test out entering the date in "MM/DD/YYYY" format and see if that works for you.
If it does, then there are some options you can pursue:
- enter the date in US Format
- use a Date Picker
- declare "Daterange" as a string and convert it to a date in VBA before applying to your filter

 

[mumps]

See if this works for you:

Sub Pomotions()
    Dim Lastrow As Long
    Lastrow = Range("A" & Rows.Count).End(xlUp).Row
    Dim Daterange As String
    Daterange = InputBox("Please enter effective date (DD/MM/YYYY)")
    ActiveSheet.Range("A1:BL" & Lastrow).AutoFilter Field:=4, Criteria1:=CDate(Daterange)
End Sub

 

[Fluff]

This is how I normally do it

Criteria1:=Format(Daterange, "[COLOR=#ff0000]dd/mm/yyyy[/COLOR]")

The part in red need to be the same format as the cells

 

[Andrew_UK]

I believe that I read that VBA uses US Date formatting.

So test out entering the date in "MM/DD/YYYY" format and see if that works for you.
If it does, then there are some options you can pursue:
- enter the date in US Format
- use a Date Picker
- declare "Daterange" as a string and convert it to a date in VBA before applying to your filter

Hi Joe, thanks for your reply. I tried the US date format and had no luck.

Also I tested using the macro recorder and then clicking "Date Filters", "Equals to"... to see how it recorded the process and here's what I got;

Sub Macro1()

    Rows("1:1").Select
    Selection.AutoFilter
    Range("D1").Select
    ActiveSheet.Range("$A$1:$BF$21059").AutoFilter Field:=4, Criteria1:= _
        "=09/11/2017", Operator:=xlAnd
End Sub

This returned data for the 9th of November as intended, seeming to imply that US/UK date formats aren't the issue.

Alternatively, If I try typing it in as a search string into the box within the filter I get it saying no results before returning the data.

https://ibb.co/ev5Xnb

I worry that this is the process which my original Macro was attempting to apply and therefore getting no results.

 

[Andrew_UK]

Hi Fluff,

Thanks for your reply. I tried that, I can see the logic in what your code says. But I couldn't seem to get it to work for me.

Interestingly, despite

Dim Daterange as Date

and despite

[COLOR=#333333]Format(Daterange, "[/COLOR][COLOR=#ff0000]dd/mm/yyyy[/COLOR][COLOR=#333333]")[/COLOR]

, when I look back at the results it still seems to have applied an erroneous text filter instead of a date filter.

I love VBA at times, but at other times it infuriates me

 

[Fluff]

This works for me

Sub Pomotions()

Dim Lastrow As Integer
Dim Daterange As Date

Lastrow = Columns("A:A").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

Daterange = InputBox("Please enter effective date (DD/MM/YYYY)")

'Rows("1:1").Select
Rows(1).AutoFilter

ActiveSheet.Range("$A$1:$BL" & Lastrow).AutoFilter Field:=4, Operator:= _
        xlFilterValues, Criteria1:=Format(Daterange, "DD/MM/YYYY")
End Sub

What does your date look like in the cell & how does it appear in the formula bar?

 

[Joe4]

It sounds like maybe either:
- your date entry are actually entered as text and not dates (can easily be confirmed with the ISNUMBER function, as dates are stored as numbers in Excel)
- there is a time component in your dates that is not being accounted for

 

[Andrew_UK]

Thanks for all your help guys,

I've got this working now!!

 

[Joe4]

Thanks for all your help guys,

I've got this working now!!

Glad to hear it!

Out of curiosity, what was the issue?