Auto Filter a Dynamic Array of Dates

Glove303

Board Regular
Joined
Dec 18, 2010
Messages
57
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!

Code:
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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,501

ADVERTISEMENT

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

Code:
[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...

Code:
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...

Code:
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!
 
Last edited:

Glove303

Board Regular
Joined
Dec 18, 2010
Messages
57
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...

Code:
[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...

Code:
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...

Code:
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,682
Messages
5,637,764
Members
416,982
Latest member
lisam77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top