Auto Filter a Dynamic Array of Dates

Glove303

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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
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
20,235
Office Version
  1. 365
Platform
  1. Windows

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
65
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!
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,235
Office Version
  1. 365
Platform
  1. Windows
You're very welcome. And thanks for your feedback.

Cheers!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,563
Messages
5,838,125
Members
430,529
Latest member
PaperBoi5870

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