autofilter <= not working

[GlennW81]

Hi,

I am trying to filter two columns, one an effective date, the second an expiry date, to filter permits that were valid for a particular year. As the year of interest changes I have set up a userform which I can enter the year of interest.
I am successfully able to get the first filter >= to work, however, the filter for the second columns does not work that contain <=.
I can get the second filter to work if I remove the < and just have equal t. But this is not ideal as I can not filter the less than dates.
I have also tried having two criteria for the same column, criteria1 equals to, and criteria less than, with the Operator:=xlfiltervalues, though this does not work either as.
When I say it does not work, the result of the filter is that no data selected/shown in the sheet, even though I can clearly see data that should be selected in the filter and shown.
Why does >= work correctly but <= does not?

The following is the line of code I refer to:

Public HPyr As Long
Dim wsHPHarv As Worksheet
Dim lastrow, lcol As Long

Sub org_Harv_Permit_sht()

Set wsHPHarv = ThisWorkbook.Worksheets("HP harvester permits")
lastrow = wsHPHarv.Range("B" & Rows.Count).End(xlUp).Row
lcol = wsHPHarv.Cells(1, Columns.Count).End(xlToLeft).Column

wsHPHarv.Range(wsHPHarv.Cells(1, 1), wsHPHarv.Cells(lastrow, lcol)).AutoFilter field:=5, Criteria1:=">=1/01/" & HPyr
wsHPHarv.Range(wsHPHarv.Cells(1, 1), wsHPHarv.Cells(lastrow, lcol)).AutoFilter field:=6, Criteria1:="<=31/12/" & HPyr

End Sub

Really appreciate any help.

Cheers

 

[kevin9999]

Where do you set the variable HPyr? If you don't set its value, it will be 0, which might explain why the first filter works, but not the second.

 

[GlennW81]

Hi @kevin9999 , If it was 0 I don't think it would work on the first line either. The variable HPyr gets its value from input in textbox of userform. I have stepped through the code to ensure the value gets passed to the module fine. For example, user inputs 2022, then HPyr is set to 2022.

I have discovered the issue appears to be the date settings in VBA. Autofilter criteria date seems to only work when the date format in the code is American date. My data (being an Australian) in the spreadsheet is in dd/mm/yyyy format. However, the criteria on the autofilter appears to be default to mm/dd/yyyy. Because 1/1 is considered 1st of Jan in both formats it works fine. Though the second line does not filter because the

Criteria1:="<=31/12/" & HPyr

considers the 31 as the month.
Is there a way to change the criteria settings? Or am I just stuck with having to remember to use the mm/dd format in the autofilter criteria?

Cheers,
Glenn

 

[kevin9999]

Dates are always a bit tricky with AutoFilter. Try the following (adjust as you see fit).

This Code

Option Explicit
Sub GlennW81()
    Dim ws As Worksheet, HPyr As Long
    Set ws = ThisWorkbook.Worksheets("HP harvester permits")
    
    'I'm getting HPyr from the sheet - you'll get it from your userform
    HPyr = ws.Range("H1")
    
    With ws.Cells(1, 1)
        .AutoFilter 5, ">=" & CLng(DateValue("1/01/" & HPyr))
        .AutoFilter 6, "<=" & CLng(DateValue("31/12/" & HPyr))
    End With

End Sub

Turns this

test.xlsm
	A	B	C	D	E	F	G	H
1	Header1	Header2	Header3	Header4	Header5	Header6		2022
2					01/01/2021	01/01/2021
3					15/06/2021	15/06/2021
4					01/01/2022	01/01/2022
5					01/06/2022	01/06/2022
6					30/12/2022	30/12/2022
7					31/12/2022	31/12/2022
8					01/01/2023	01/01/2023
9					01/06/2023	01/06/2023
10					01/06/2022	01/06/2022
11					30/12/2022	30/12/2022
12
HP harvester permits

Into this

test.xlsm
	A	B	C	D	E	F	G	H
1	Header1	Header2	Header3	Header4	Header5	Header6		2022
4					01/01/2022	01/01/2022
5					01/06/2022	01/06/2022
6					30/12/2022	30/12/2022
7					31/12/2022	31/12/2022
10					01/06/2022	01/06/2022
11					30/12/2022	30/12/2022
12
HP harvester permits

Is that what you want?

 

[kevin9999]

Another way...

Sub GlennW81_2()
    Dim ws As Worksheet, HPyr As Long
    Set ws = ThisWorkbook.Worksheets("HP harvester permits")
    
    'I'm getting HPyr from the sheet - you'll get it from your userform
    HPyr = ws.Range("H1")
    
    With ws.Cells(1, 1)
        .AutoFilter 5, ">=" & DateSerial(HPyr, 1, 1)
        .AutoFilter 6, "<=" & DateSerial(HPyr, 31, 12)
    End With

End Sub