Help on Autofilter operator

[mcguirpa]

I have a spreadsheet that I'm looking to filter on two criteria: Date created and date changed, the format for Date created is YYYYMM

This is the code I'm trying to use:

Dim LastCreated As String
LastCreated = Application.InputBox("Enter required 'created month' in format YYYYMM")

'filter on change date = blank and created date = LastCreated

With Worksheets("Filtered").Range("A1")

.AutoFilter field:=26, Criteria1:=""
.AutoFilter field:=5, Criteria1:=">=" & LastCreated

End With

The issue I have is that this works fine as long as the second criterion is "=", but if I change it to ">=" then everything is filtered out even though there are entries that are definitely >= LastCreated, but I don't know what I'm doing wrong, or why I'm getting this behaviour. Can anyone give me any pointers on how this is working/why it's not working as I intended?

[JEC.]

Try:

(you should use complete date format as well): 12-29-2021

Please Login or Register  to view this content.

[mcguirpa]

Tried this but no joy. I had a feeling it wouldn't work as I'd previously tried:

'prompt for date of last report
Dim LastCreated As String
LastCreated = Application.InputBox("Enter required 'created month' in format YYYYMM")

'convert LastCreated to Long
Dim LastCreatedLong As Long
LastCreatedLong = CLng(LastCreated)

'do filter on change date

With Worksheets("Filtered").Range("A1")

.AutoFilter field:=26, Criteria1:=""
.AutoFilter field:=5, Criteria1:=">=" & LastCreatedLong

End With

There's obviously something going on I don't understand.

One other question - why would I need the full date format? If I want anything created in September 2021 or beyond then if the table says '202109' I can't understand why filtering for >=202109 doesn't work. Weirdly if I just want =202109 it works fine.

[JEC.]

Well try to include the first of the month then, like 9-1-2021 and run this code. That should be working

Please Login or Register  to view this content.

[mcguirpa]

Well try to include the first of the month then, like 9-1-2021 and run this code. That should be working

Please Login or Register  to view this content.

OK, I'll give it a go. But can anyone explain what is going on? What if this had just been a number and not a date? How would I generalise this to any data type?

[mcguirpa]

fixed it by changing the created date column to a number using this code:

With Range("E2:E" & Cells(Rows.Count, 1).End(xlUp).Row)
.NumberFormat = "General"
.Value = .Value

End With

now all works fine.

[TMS]

@mcguirpa: for future reference, please provide a sample workbook. It takes a lot of the guesswork out of diagnosong the problem and providng a solution.

And ... Please don't quote whole posts -- it's just clutter.*If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding