Excel Autofilter Criteria array loop

Question

I need to filter a column by dates. I determine the number of dates needed, and then apply the filter. Originally I figured there were only 2 cases, Tuesday thru Friday where you use the previous date. And Monday where Friday, Saturday and Sunday are needed.

Then I realized that there might be a holiday on Friday or Monday, and it would require more dates...and there might be a midweek holiday, like Thanksgiving. I then came up with this to cover the 4 most common possibilities:

  dPosting = dLast + 1 'dLast is the last Date used in the filter
  Select Case Date - dLast
    Case 2: 'Regular T - F (1 day)
      shFF.Range("A1", shFF.Cells(lMax, lCol)).AutoFilter Field:=cFind.Column, Operator:=xlFilterValues, Criteria1:=Array(2, dPosting)
    Case 3: 'Midweek Holiday (2 days)
      shFF.Range("A1", shFF.Cells(lMax, lCol)).AutoFilter Field:=cFind.Column, Operator:=xlFilterValues, Criteria1:=Array(2, dPosting, 2, dPosting + 1)
    Case 4: 'Regular Monday (3 days)
      shFF.Range("A1", shFF.Cells(lMax, lCol)).AutoFilter Field:=cFind.Column, Operator:=xlFilterValues, Criteria1:=Array(2, dPosting, 2, dPosting + 1, 2, dPosting + 2)
    Case 5: 'After F or M Holiday (4 days)
      shFF.Range("A1", shFF.Cells(lMax, lCol)).AutoFilter Field:=cFind.Column, Operator:=xlFilterValues, Criteria1:=Array(2, dPosting, 2, dPosting + 1, 2, dPosting + 2, 2, dPosting + 3)
    Case Else: 'Other
      MsgBox "Too many days since last run.  Please contact me", vbCritical, "Out of Scope"
  End Select

Then I figured, what if the person that runs this is out for a week for vacation...and threw in the Case Else.

Now I think that the easiest and cleanest way will be to simply determine the number of dates needed from the last date that was run dLast and Now. Put that into an array and use that as the Criteria...but I can't figure out the syntax.

Checked these, but still but can't make it work:

This is my test code to play with the syntax and data sheet:

Sub Test()
Dim i As Integer
Dim d As Date
Dim sArr(0 To 2) As String

  d = #4/25/2016#
  For i = 0 To 2
    sArr(i) = "2, """ & d + i & """"
  Next i
  ActiveSheet.Range("$A$1:$R$16643").AutoFilter Field:=17, Operator:=xlFilterValues, Criteria1:=sArr

End Sub

The macro recorder spits this out for using 3 dates:

    ActiveSheet.Range("$A$1:$R$16643").AutoFilter Field:=17, Operator:=xlFilterValues, _
Criteria2:=Array(2, "4/25/2016", 2, "4/26/2016", 2, "4/27/2016")

What am I missing??

You could have the public bank holidays in the sheet, then do something like dim r as range: set r=find(date,public holidays):if r is nothing, then not bank holiday through your dates, or use application.countif(public hols, date) You could also try trapping the error adding each filter in turn, so if 01/01/16 isn't in the filter it will error, then move on to the next one. — Nathan_Sav
– Nathan_Sav, Commented May 5, 2016 at 16:24
I thought about that, but this is for a user that would then have to update that Holiday list possibly...and that would be a nightmare. Plus, they still want to be able to check those dates regardless of holiday status. — Rdster
– Rdster, Commented May 5, 2016 at 16:51

Nathan_Sav · Accepted Answer · 2016-05-05 16:28:28Z

1

Could you use something like this, and calculate the dates, dt1 and dt2, using Date - dLast, instead of the selects, and try

.AutoFilter Field:=2, Criteria1:= ">=" & dt1, Operator:=xlAnd, Criteria2:="<=" & dt2

answered May 5, 2016 at 16:28

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rdster Over a year ago

That worked for me. I like this because I can also get rid of the Array. The days will be consecutive, so this should work fine. Thanks.

Collectives™ on Stack Overflow

Excel Autofilter Criteria array loop

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related