Filtering from Tuesday to Tuesday Each Week-CodePudding

I have a code (below) where I can filter based on last week using just the date function and subtracting by 7. Is there a way I can do this where I filter from say Tuesday to Tuesday each week? I know there is the vbTuesday function but not sure why I can't recall the correct way to pull that.

See code:

Dim j As Integer,
j = Application.WorksheetFunction.Match("Date and Time Completed", Range("A1:BG1"), 0)
Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).AutoFilter Field:=j, Criteria1:=">=" & CLng(Date - 7)

CodePudding user response：

You need to format the date condition as as string-date:

Criteria1:=">=" & Format(Date - 7, "dd\/mm\/yyyy")

CodePudding user response：

First, find the last Tuesday and the Wednesday before and format these.

Dim PreviousWednesday As String
Dim LastTuesday As String

PreviousWednesday = Format(DateAdd("ww", -1, DatePreviousWeekday(date, vbWednesday)), "m\/d\/yyyy")
LastTuesday = Format(DatePreviousWeekday(date, vbTuesday), "m\/d\/yyyy")

' To debug:
? PreviousWednesday, LastTuesday
' Result:
' 7/6/2022      7/12/2022

Those functions are from module DateFind.bas from my library VBA.Date.

Then compose the criteria:

Criteria1:=">=" & PreviousWednesday & "", Operator:=xlAnd, Criteria2:="<=" & LastTuesday & ""