Auto Filter a Dynamic Array of Dates

[Glove303]

Hi,#

Could someone tell me why this isn't working? I can get it to work for non-dates (obviously changing the column in the autofilter to one that accepts the relevant values), but dates are proving a problem!

Sub AM()

Dim Root As String
Dim DteAry() As Date
Dim n As Long


Sheets("Parameters").Select
Window = Cells(2, 1)


Root = Cells(1, 1)


Dim numcounts As Integer
numcounts = Application.WorksheetFunction.CountA(Range("A4:A1048576"))


For Each iCell In Range(Cells(4, 3), Cells(6, 3))
    If iCell.Value <> " " Then
        n = n + 1
        ReDim Preserve DteAry(1 To n)
        DteAry(n) = iCell.Value
    End If
Next iCell


For i = 1 To numcounts


    Sheets("Parameters").Select
    web = Cells(i + 3, 1)


    Workbooks.Open Filename:= _
    Root & web & ".xlsx"
      
    Range("A2:J2").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$2:$J$5000").AutoFilter Criteria1:=DteAry, Field:=2, Operator:=xlFilterValues

End sub

Many thanks,
James.

 

[pgc01]

Hi James

There's no Date type in the worksheet, only in vba.
Dates in the worksheet are just numbers

Try defining DteAry() as Double.

 

[pgc01]

Sorry, I meant as DteAry() as String.

 

[Glove303]

Sorry, I meant as DteAry() as String.

Thanks, unfortunately I already tried that. There seems to be something tricky with getting Excel to accept dates in the array.

 

[Domenic]

First, try creating an array with the following pattern...

[COLOR=#4E4E4E][FONT=Courier]Array(2, date, 2, [/FONT][/COLOR][COLOR=#4E4E4E][FONT=Courier]date[/FONT][/COLOR][COLOR=#4E4E4E][FONT=Courier], 2, [/FONT][/COLOR][COLOR=#4E4E4E][FONT=Courier]date[/FONT][/COLOR][COLOR=#4E4E4E][FONT=Courier])[/FONT][/COLOR]

...where 2 specifies that you want the day. So, for example, you can do the following...

n = 0
For Each iCell In Range(Cells(4, 3), Cells(6, 3))
    If iCell.Value <> " " Then
        n = n + 2
        ReDim Preserve DteAry(1 To n)
        DteAry(n - 1) = 2 'day
        DteAry(n) = iCell.Value
    End If
Next iCell

Then you'll need to use Criteria2, not Criteria1...

ActiveSheet.Range("$A$2:$J$5000").AutoFilter Criteria2:=DteAry, Field:=2, Operator:=xlFilterValues

Have a look here for more details...

https://www.excelcampus.com/vba/filter-dates/

Hope this helps!

 

[Glove303]

Domenic,

Thank you very much, this is exactly what I needed and works a treat!

Cheers,
James.

First, try creating an array with the following pattern...

[COLOR=#4E4E4E][FONT=Courier]Array(2, date, 2, [/FONT][/COLOR][COLOR=#4E4E4E][FONT=Courier]date[/FONT][/COLOR][COLOR=#4E4E4E][FONT=Courier], 2, [/FONT][/COLOR][COLOR=#4E4E4E][FONT=Courier]date[/FONT][/COLOR][COLOR=#4E4E4E][FONT=Courier])[/FONT][/COLOR]

...where 2 specifies that you want the day. So, for example, you can do the following...

n = 0
For Each iCell In Range(Cells(4, 3), Cells(6, 3))
    If iCell.Value <> " " Then
        n = n + 2
        ReDim Preserve DteAry(1 To n)
        DteAry(n - 1) = 2 'day
        DteAry(n) = iCell.Value
    End If
Next iCell

Then you'll need to use Criteria2, not Criteria1...

ActiveSheet.Range("$A$2:$J$5000").AutoFilter Criteria2:=DteAry, Field:=2, Operator:=xlFilterValues

Have a look here for more details...

https://www.excelcampus.com/vba/filter-dates/

Hope this helps!

 

[Domenic]

You're very welcome. And thanks for your feedback.

Cheers!