CODEWITHSUNDEEP
excelvba
Snehil
Snehil

Reputation: 55

Dynamically filter year which also contains text

The column Q that I am trying to filter has category called 2018 Complete present as string. I have been looking for answers to see how could I filter out 2018 Complete if I was in 2019 and 2019 Complete if I was in 2020. I did make a code but it does not work. Is there a way I can make it more dynamic so, if I was present in any other year I could still use it?

With Sheets("Valve List")
    .AutoFilterMode = False
    .Range("Q1").AutoFilter Field:=17, Criteria1:=" & Year(Date) - 1 & " & "Complete"
End With

The Code works just fine If I use it with only text:

.Range("Q1").AutoFilter Field:=17, Criteria1:="2018 Complete"

Upvotes: 0

Views: 25

Answers (1)

Ricardo Diaz
Ricardo Diaz

Reputation: 5696

Set the criteria to

With Sheets("Valve List")
    .AutoFilterMode = False
    .Range("Q1").AutoFilter Field:=17, Criteria1:= Year(date) -1 & " Complete"
End With

Upvotes: 1

Related Questions