Excel array filter

F

fanku

New Member
Joined
Sep 20, 2006
Messages
30
please help.. unable to filter arry based on fromdate

Sub test_array()
'Initialise Array
Dim myArray As Variant
myArray = Sheets("Public Holidays").Range("A2:A30").Value ' -- all dates stored in date format i.e. dd-mmm-yy
fromdate = Range("E2").Value '--dd-mm-yy


Z = Filter(myArray, fromdate) '-- error data mismatch

If UBound(Z) > 0 Then ctr = ctr + 1
MsgBox Z


End Sub
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Perhaps delcare the date as long.

This code will filter dates for you

Sub Macro1()
'Code to apply a filter for dates
Dim Criteria As Long
Criteria = Range("E2")

ActiveSheet.Range("$A$1:$A$32").AutoFilter Field:=1, Criteria1:="<" & Criteria, Operator:=xlAnd
End Sub
Click to expand...

Public Holidays

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 75px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 72px"><COL style="WIDTH: 75px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Dates</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">01/09/2011</TD><TD> </TD><TD> </TD><TD>Filter Date</TD><TD style="TEXT-ALIGN: right">07/09/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">02/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">03/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">04/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">05/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">06/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">07/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">08/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">09/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right">10/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: right">11/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: right">12/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: right">13/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: right">14/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: right">15/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: right">16/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: right">17/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: right">18/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: right">19/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: right">20/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="TEXT-ALIGN: right">21/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD style="TEXT-ALIGN: right">22/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD style="TEXT-ALIGN: right">23/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD style="TEXT-ALIGN: right">24/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD style="TEXT-ALIGN: right">25/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">27</TD><TD style="TEXT-ALIGN: right">26/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">28</TD><TD style="TEXT-ALIGN: right">27/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">29</TD><TD style="TEXT-ALIGN: right">28/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">30</TD><TD style="TEXT-ALIGN: right">29/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
Click to expand...

Result

Public Holidays

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 75px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 72px"><COL style="WIDTH: 75px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Dates</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">01/09/2011</TD><TD> </TD><TD> </TD><TD>Filter Date</TD><TD style="TEXT-ALIGN: right">07/09/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">02/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">03/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">04/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">05/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">06/09/2011</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
Click to expand...
 
Upvote 0
fanku said:
please help.. unable to filter arry based on fromdate

Sub test_array()
'Initialise Array
Dim myArray As Variant
myArray = Sheets("Public Holidays").Range("A2:A30").Value ' -- all dates stored in date format i.e. dd-mmm-yy
fromdate = Range("E2").Value '--dd-mm-yy


Z = Filter(myArray, fromdate) '-- error data mismatch

If UBound(Z) > 0 Then ctr = ctr + 1
MsgBox Z


End Sub
Click to expand...
Unless I'm much mistaken, I don't believe you can apply a filter to an array in this way, although I'd be very interested to discover if it is possible.

My guess is you'll have to apply the filter in sheet and then copy the visible rows into your array.
 
Upvote 0
Try transposing the array:

Code: 
myArray = Application.Transpose(Sheets("Public Holidays").Range("A2:A30").Value)

Weaver said:
Unless I'm much mistaken, I don't believe you can apply a filter to an array in this way, although I'd be very interested to discover if it is possible.
Click to expand...
@Weaver: Have a look at the Filter function in your VBA help file. ;)

I don't like the Filter function much. With text filters it operates a 'contains' expression. I almost always use my own function.
 
Last edited:
Upvote 0
Thank you for prompt reply...

but how can i check count in filter mode..
just i wish to check data in filter..if data in filter then CAL_PUBHOL = CAL_PUBHOL + 1

Z = Filter(myArray, fromdate)
If Z > 0 Then
CAL_PUBHOL = CAL_PUBHOL + 1
End If

Pls help..
 
Upvote 0
If Ubound(Z) is > 0 then there were matching values.

Can you explain in words what you are actually trying to achieve?
 
Upvote 0
sir..i wish to find public holiday between following dates.

e2 = 31-Aug-11 '- this is known as fromdate
F2 = 16-Sep-11 '-- todate

list of public holidays in different sheets which we copied into array ..as you suggested.
Dates
01-Sep-11
06-Sep-11
15-Sep-11
31-Aug-11

so i have written following codes...

Function calc_PUBHOL(fromdt, todt)
fromdate = fromdt
todate = todt
PUBHOL = 0
'Initialise Array
Dim myArray As Variant
myArray = Application.Transpose(Sheets("Public Holidays").Range("A2:A30").Value)

Do While fromdate <= todate
Z = Filter(myArray, fromdate)

''' -- here i need your help..if data availble in filter mode then pubholiday = pubholida + 1..pls help me how to do that...

If Z > 0 Then
PUBHOL = PUBHOL + 1
End If
fromdate = fromdate + 1
Loop
calc_PUBHOL = PUBHOL

End Function






z = i wish to count data in filter mode
if z >0 then
ctr = ctr + 1
endif
 
Upvote 0
I may be wrong but I expect you are looking for a VBA function to count the number of dates within a range based on a start date and end date. If so I would entirely abandon the array / filter method that you are using and go with CountIf...

Code: 
Public Function calc_PUBHOL(ByVal rngDates As Range, ByVal lngStart As Long, ByVal lngEnd As Long) As Long
    With Application
        calc_PUBHOL = .CountIf(rngDates, ">=" & lngStart) - _
                      .CountIf(rngDates, ">" & lngEnd)
    End With
End Function
Code: 
Public Sub Example()
    MsgBox calc_PUBHOL(Sheets("Public Holidays").Range("A2:A30"), _
                       DateValue("31-Aug-2011"), _
                       DateValue("16-Sep-2011"))
End Sub
 
Upvote 0
Jon von der Heyden said:
@Weaver: Have a look at the Filter function in your VBA help file. ;)
Click to expand...
I guess I was much mistaken!

Can't think of a time where I might've needed this (don't work that much in internal arrays, apart from when using the scripting dictionary) otherwise I might've come across it sooner! (I bet I'll think of some application for it now)Thanks for the heads up.
 
Upvote 0
Weaver said:
...don't work that much in internal arrays, apart from when using the scripting dictionary)...
Click to expand...

I've taken to using arrays quite alot these days. Another little known fact is that it is quicker to loop through a variant array than though a range of cells. Thus, where one might typically use a For Each rngCell block I have adopted a process of loading and array, processing it within a loop and then unloading the array back to the range.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Compile error: Wrong number of arguments or invalid property assignment
Replies
2
Views
74
daveyc18
D
O
  • Question Question
User-defined type not defined - vba/excel error
Replies
4
Views
276
RoryA
RoryA
H
  • Question Question
Advanced Multi VBA - Creating multiple temporary files and attach them to one email on outlook
Replies
2
Views
279
Hayf13
H
L
  • Question Question
VBA to export recurring meeting data
Replies
1
Views
1K
John_w
J
S
  • Question Question
VBA, OLAP cube pivot : refer to parent of selected filter?
Replies
1
Views
530
srgexcel
S

Forum statistics

Threads
1,224,616
Messages
6,179,910
Members
452,949
Latest member
beartooth91

Share this page

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
Back
Top