Filter Dates Stored In An Array

[fiberboysa]

Hi,
I have a simple worksheet which has many dates in an column and I want to filter only Sundays from them. This is a very easy task if you can add a column in data but you cant. So as far as I know it can be done if you store all these dates in an Array in VBA and then filter that column with this Array.
I got a few codes from different website and make them to store all Sundays in that column in an Array but I can't apply filter to that column with this array. So its requested to please suggest a way to do the job. Following is my code for your reference...

Sub Filter_Sundays()
Dim LR As Integer
Dim Sundays() As Variant
ReDim Sundays(0)
    LR = Workbooks("Filter Sundays").Sheets("Sheet1").Cells(Workbooks("Filter Sundays").Sheets("Sheet1").Rows.Count, "V").End(xlUp).Row
    For x = 6 To LR
        If Weekday(Range("V" & x)) = 1 Then
            ReDim Preserve Sundays(0 To UBound(Sundays) + 1) As Variant
            Sundays(UBound(Sundays)) = Range("v" & x)
        End If
    Next
    ActiveSheet.Range("$V$5:" & LR).AutoFilter Field:=1, Operator:= _
        xlFilterValues, Criteria1:=Array(Application.Transpose(Sundays))
End Sub

Thanks in advance!

 

[footoo]

Try the following changes :

Instead of:

Sundays(UBound(Sundays)) = Range("v" & x)

Change to:

Sundays(UBound(Sundays)) = Format(Range("v" & x), "mm-dd-yyyy")

Amend "mm-dd-yyyy" to the actual format used in column V

Instead of :

 ActiveSheet.Range("$V$5:" & LR).AutoFilter Field:=1, Operator:= _
        xlFilterValues, Criteria1:=Array(Application.Transpose(Sundays))

Change to:

ActiveSheet.Range("$V$5:$V" & LR).AutoFilter Field:=1, Criteria1:=Sundays, _
    Operator:=xlFilterValues

 

[fiberboysa]

Try the following changes :

Instead of:

Sundays(UBound(Sundays)) = Range("v" & x)

Change to:

Sundays(UBound(Sundays)) = Format(Range("v" & x), "mm-dd-yyyy")

Amend "mm-dd-yyyy" to the actual format used in column V

Instead of :

 ActiveSheet.Range("$V$5:" & LR).AutoFilter Field:=1, Operator:= _
        xlFilterValues, Criteria1:=Array(Application.Transpose(Sundays))

Change to:

ActiveSheet.Range("$V$5:$V" & LR).AutoFilter Field:=1, Criteria1:=Sundays, _
    Operator:=xlFilterValues

Worked perfect... I clanged the code to following and it worked. Thank you!

Sub Filter_Sundays()
Dim LR As Integer
Dim Sundays() As Variant
ReDim Sundays(0)
    LR = Workbooks("Filter Sundays").Sheets("Sheet1").Cells(Workbooks("Filter Sundays").Sheets("Sheet1").Rows.Count, "V").End(xlUp).Row
    For x = 6 To LR
        If Weekday(Range("V" & x)) = 1 Then
            ReDim Preserve Sundays(0 To UBound(Sundays) + 1) As Variant
            Sundays(UBound(Sundays)) = Format(Range("v" & x), "[$-x-sysdate]dddd, mmmm dd, yyyy")
        End If
    Next
    ActiveSheet.Range("$V$5:$V" & LR).AutoFilter Field:=1, Criteria1:=Sundays, _
    Operator:=xlFilterValues
End Sub