Filter Function

  • Thread starter Thread starter David Robinson
  • Start date Start date
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)
 
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
 
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
 
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
 
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.
 
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
 
Back
Top