can someone post an example of the Filter Function in VB.

David,

If you check out this previous post, you will see an example of creating a
column to test a value, filtering on that value, and then deleting the
filtered rows. Hopefully it will show you what you want

http://tinyurl.com/v4qk

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"David Robinson" wrote in message
...
can someone post an example of the Filter Function in VB.

Here's one:

Sub VBAFilterExample()

'Criteria used in search. Change as desired
Const szCRITERIA As String = "rob"

Dim lCount As Long
Dim vOrigArray As Variant
Dim vFilterArray As Variant
Dim szMsgOrig As String, szMsgFilter As String

'Create an array
vOrigArray = Array("Robert", "Rob", "Mac", "Bert", "Sam")

'Store the contents of the array in a string for later use
For lCount = LBound(vOrigArray) To UBound(vOrigArray)
szMsgOrig = szMsgOrig & vOrigArray(lCount) & ", "
Next lCount

'Filter the array based on the criteria. The result is another Array
with just the filtered results
'Note: True = retrieve all that match the criteria. False = retrieve all
that DON'T match the criteria
vFilterArray = Filter(vOrigArray, szCRITERIA, True, vbTextCompare)

'Store the contents of the new filtered array in a string for later use
For lCount = LBound(vFilterArray) To UBound(vFilterArray)
szMsgFilter = szMsgFilter & vFilterArray(lCount) & ", "
Next lCount

'Show the original array, the criteria, and the filtered (resulting)
array in a Message Box
MsgBox "Original Array: " & szMsgOrig & vbLf & vbLf & "Criteria: " &
szCRITERIA & vbLf & vbLf & "Resulting Array: " & szMsgFilter, vbInformation,
"Filter Function Example"
End Sub
_______________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel

"David Robinson" wrote in message
...
can someone post an example of the Filter Function in VB.

Sub Tester1()
varr = Array("ABCD", "ADCE", "BCDA", "1234", "ECDA")
varr1 = Filter(varr, "BC", True, vbTextCompare)
For i = LBound(varr1) To UBound(varr1)
Debug.Print varr1(i)
Next
End Sub

--
Regards,
Tom Ogilvy


David Robinson wrote in message
...
can someone post an example of the Filter Function in VB.

Dear Bob

Actually I was referring to a function I just found in
the help screen which "Returns a zero-based array
containing subset of a string array based on a specified
filter criteria". Any example of this:

Filter(sourcesrray, match[, include[, compare]])

David

-----Original Message-----
David,

If you check out this previous post, you will see an
example of creating a
column to test a value, filtering on that value, and
then deleting the
filtered rows. Hopefully it will show you what you want

http://tinyurl.com/v4qk

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"David Robinson"
wrote in message
...
can someone post an example of the Filter Function in
VB.


.

Thankyou for this Tom

Can I get this to work on multidimensional arrays?

-----Original Message-----
Sub Tester1()
varr = Array("ABCD", "ADCE", "BCDA", "1234", "ECDA")
varr1 = Filter(varr, "BC", True, vbTextCompare)
For i = LBound(varr1) To UBound(varr1)
Debug.Print varr1(i)
Next
End Sub

--
Regards,
Tom Ogilvy


David Robinson
wrote in message
...
can someone post an example of the Filter Function in
VB.


.

Not directly. The help on filter cites sourcearray (the first argument) as a
one dimensional array of strings.

While you could program around this to extract the filter column, use filter
to build an array of those row "indexes" that meet the criteria and so
forth, it might be just as easy to go through your array and do a simple if
test - then put each matching row in another array.

--
Regards,
Tom Ogilvy


wrote in message
...

Thankyou for this Tom

Can I get this to work on multidimensional arrays?

-----Original Message-----
Sub Tester1()
varr = Array("ABCD", "ADCE", "BCDA", "1234", "ECDA")
varr1 = Filter(varr, "BC", True, vbTextCompare)
For i = LBound(varr1) To UBound(varr1)
Debug.Print varr1(i)
Next
End Sub

--
Regards,
Tom Ogilvy


David Robinson
wrote in message
...
can someone post an example of the Filter Function in
VB.


.

Just two cents. I have never gotten this function to filter out blank
strings. This mostly occurs when placing a column of data from the
worksheet into an array. I have tried everything imaginable in the past.
Does anyone know if this "feature" to filter out blank strings was added in
Excel 2003?

Sub Wont_Work_in_Xp()
Dim v
v = Array("ABCD", "ADCE", "", "1234", "ECDA")
v = Filter(v, "", False, vbTextCompare)
End Sub

Returns an empty array.


If one does not like the zero based returned array, here is a technique to
turn it back into a 1-based array.

Sub Demo()
Dim v
Dim v_ZeroBased
Dim v_OneBased

v = Array("ABCD", "ADCE", "BCDA", "1234", "ECDA")
v_ZeroBased = Filter(v, "BC", True, vbTextCompare)

With WorksheetFunction
v_OneBased = .Transpose(.Transpose(v_ZeroBased))
End With
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"David Robinson" wrote in message
...
can someone post an example of the Filter Function in VB.