PDA

View Full Version : [SOLVED] Date filter in table



werafa
05-02-2019, 02:05 PM
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
05-02-2019, 10:38 PM
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
05-02-2019, 10:52 PM
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