Need VBA Assistance - Filter column if cells contain current month / year

[sparkytech]

I have column "W" with text comments that also contain dates. For example: "abc ~ 06.01.22 ~ This is a sample comment!"

I need help creating a filter to look at column "W", and only show rows that contain the current month / year in this string of text in cell "W'. My thought is that it could look for the string: {current month number}.**.{current year}. I hope that makes sense... is this possible?

 

[DanteAmor]

Try this:

Sub Macro1()
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  Range("A1", Range("W" & Rows.Count).End(3)).AutoFilter Field:=23, Criteria1:="*" & Format(Date, "mm") & ".*" & Format(Date, "yy") & "*"
End Sub

 

[sparkytech]

Try this:

Sub Macro1()
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  Range("A1", Range("W" & Rows.Count).End(3)).AutoFilter Field:=23, Criteria1:="*" & Format(Date, "mm") & ".*" & Format(Date, "yy") & "*"
End Sub

Thanks! This works, but needs a tweak. When I run this, it does correctly show a row with 06.01.22 in the text string in column "W", but also shows a row with 04.06.22. (which needs to be filtered out, as 04 is not the current month). I think the filter sees the 06.22 and decides that is current month, even though its from April. I'm still learning VBA... does this (or can it) look at only the first 2 digits as month, and the last 2 digits as year, and ignore the middle 2 digits? Also if its not too much to ask, can it filter/show current and prior month? Thanks again!

 

[DanteAmor]

Also if its not too much to ask, can it filter/show current and prior month?

Try this:

Sub Macro1()
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  Range("A1", Range("W" & Rows.Count).End(3)).AutoFilter Field:=23, _
    Criteria1:="*" & Format(Date, "mm") & ".*." & Format(Date, "yy") & "*", _
    Operator:=xlOr, _
    Criteria2:="*" & Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mm") & ".*." & Format(Date, "yy") & "*"
End Sub

 

[sparkytech]

Try this:

Sub Macro1()
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  Range("A1", Range("W" & Rows.Count).End(3)).AutoFilter Field:=23, _
    Criteria1:="*" & Format(Date, "mm") & ".*." & Format(Date, "yy") & "*", _
    Operator:=xlOr, _
    Criteria2:="*" & Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mm") & ".*." & Format(Date, "yy") & "*"
End Sub

That works perfect, thanks!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.