Filter to include blanks OR current month

[ah2024_2024]

Here I go again. Column M contains termination dates for employees and if they are still with the company then they are blank. But work is picking up the current month correctly but it's not accounting for the blanks (or active people. What am I missing?

Below is the piece of code I'm using for this part. It's like it's just skipping CRITERIA2:=Array("=) and I assume it's something to do with my Operator BUT if I change that then it doesn't work.

 

[Max1616]

Try something like this:

rng.AutoFilter Field:=13, Criteria1:=Array("="), Operator:=xlFilterValues, Criteria2:=Array(1, Format(Now(), "MM/DD/YYYY"))

This will filter for any blanks and anything in the current month

 

[ah2024_2024]

Try something like this:

rng.AutoFilter Field:=13, Criteria1:=Array("="), Operator:=xlFilterValues, Criteria2:=Array(1, Format(Now(), "MM/DD/YYYY"))

This will filter for any blanks and anything in the current month

That ended up working great! But, now my copy paste isn't working correctly any longer. It should only copy from filter data and it's copying no matter what is visible as well. The piece I put in bold and green below was copying adjacent cells (column D to be exact) only from what was visible after the auto filter. But, now it's grabbing everything if FORT WORTH is in column F and it should then paste in A9 on my other sheet.

Dim target As Worksheet

Set wssource = Worksheets("Sheet3")
Set wstarget = Worksheets("ftw")

If Not wssource.AutoFilterMode Then
wssource.Range("A11").AutoFilter
End If

wssource.Activate
wssource.Range("a1" & lr).AutoFilter Field:=6, Criteria1:="Fort Worth"
wssource.Range("a1" & lr).AutoFilter Field:=13, Criteria1:=Array("="), _
Operator:=xlFilterValues, Criteria2:=Array(1, Format(Now(), "MM/DD/YYYY"))
wssource.Range("a1" & lr).AutoFilter Field:=9, Criteria1:="REP"

For Each c In wssource.Range("f1:f20000")
If c = "FORT WORTH" Then
If Range("A9") = "" Then
c.Offset(0, -2).Copy Range("A9")
Else
c.Offset(0, -2).Copy wstarget.Cells(wstarget.Rows.Count, "A").End(xlUp).Offset(1)
End If
End If

Next c

Sheets("FTW").Range("A8:a80").RemoveDuplicates Columns:=Array(1), Header:=xlYes

Application.ScreenUpdating = True


End Sub

 

[Max1616]

Try replacing the first if statement under the loop with this:

If c = "FORT WORTH" And c.EntireRow.Hidden = False Then

This will evaluate if the cell in column F = "FORT WORTH" AND if the row is not hidden (i.e. if the row is hidden, it will be ignored in this loop)

 

[ah2024_2024]

Thanks for that, but I actually (and I'm extremely proud) got it working later that same evening! But, not my array formula isn't working and I will post that in another thread.