Date filter in table

[werafa]

Hi all,

I have a table of staff names with, where applicable, employment end dates.
I wish to filter the table to show all staff relevant to the current financial year
This requires that I select dates in the current fin year as well as fields with no date

my current code results in no records being selected. can anyone see my error?

thanks
Werafa

Private Sub tglHideYears_Click()
Dim mysheet As Worksheet
Dim myTable As ListObject
Dim filterDate As String

Set mysheet = ThisWorkbook.Worksheets("Staff List")
Set myTable = mysheet.ListObjects("tblStaff")

    filterDate = Now()
    If Month(filterDate) > 6 Then
        filterDate = "30/6/" & Year(filterDate)
    Else
        filterDate = "30/6/" & Year(filterDate) - 1
    End If
    

    If tglHideYears = True Then
        'the problem code
        myTable.Range.AutoFilter Field:=4, Criteria1:="> 30/6/2018", Operator:=xlAnd, Criteria2:=""""""
        tglHideYears.Caption = "Show All Years"
    End If
    If tglHideYears = False Then
        myTable.Range.AutoFilter Field:=4
        tglHideYears.Caption = "Show Current Year"
    End If
    
End Sub

update:

myTable.Range.AutoFilter Field:=4, Criteria1:="> 30/6/2018", Operator:=xlOr, Criteria2:="="

causes the 'no employment end dates' to show correctly, but I still do not have the row with EED = 28/11/2018

Thanks

[georgiboy]

Try:

Criteria1:=">" & Format(filterDate, "mm/dd/yyyy"), Operator:=xlOr, Criteria2:="="

Think i have come across this issue before, autofilter within VBA only seems to work with the American date format "mm/dd/yyyy"
The above just forces the filterDate to be in the American format, this solved my issue in the past.

Hope this helps

[werafa]

Thanks georgiboy,

this did work. For reference to anyone else reading this, what I did not glean from other pages is that the format in the vba code does not have to match the worksheet formatting.


the final full line of code is:

myTable.Range.AutoFilter Field:=4, Criteria1:=">" & Format(filterDate, "mm/dd/yyyy"), _
            Operator:=xlOr, Criteria2:="="

this gives me the filter by date as well as leaving any row with no entry

Thanks
Werafa