Filter Function

[David Robinson]

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

 

[Bob Phillips]

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)

 

[Robert Rosenberg]

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

 

[Tom Ogilvy]

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

 

[David Robinson]

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

 

[Guest]

Thankyou for this Tom

Can I get this to work on multidimensional arrays?

 

[Tom Ogilvy]

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.

 

[Dana DeLouis]

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