Filtering out Numbers from Array

Question

So I have an Array called TagOptions - it contains numeric values according to a pervious if statement. In order to take out values I didn't want I gave the undesired values a place holder value of 0. I am now trying to filter out this value but can't find anything online that is helpful.

Will paste the entire function for context but more interested in just filtering out the placeholder zeros from my array.

Sorry if this is novice but I am very new to this:

Private Sub CommandButton4_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("TEST")
lrow = sh.Cells(Rows.count, 1).End(xlUp).Row
Dim splitstring As String
Dim holder As String
Dim myarray() As String
Dim strArrayNumber() As Integer
Dim strArrayTag() As String
Dim TagOptions() As Integer
Dim TagOptions2() As Integer
ReDim strArrayNumber(1 To lrow) As Integer
ReDim strArrayTag(1 To lrow) As String


'Initial for loop splitting tags and removing any tags with text (MV-4005A)
'Transfering those remaining tag numbers into array if they match equip selected

For a = 1 To lrow

If sh.Cells(a, 1).Value <> vbNullString Then
splitstring = sh.Cells(a, 1).Value
myarray = Split(splitstring, "-")
strArrayTag(a) = myarray(0)
End If

If IsNumeric(myarray(1)) = False Then

myarray(1) = 0

End If

If strArrayTag(a) = TagNumber1.Value Then 'Only stored if has selected Equipment tag
strArrayNumber(a) = myarray(1)
End If

Next a

'Sort Created Array

Quicksort strArrayNumber, LBound(strArrayNumber), UBound(strArrayNumber)

ReDim TagOptions(1000 To 2000) As Integer

Dim j As Integer
For j = 1000 To 2000

    For b = 1 To UBound(strArrayNumber)

        If strArrayNumber(b) = j Then

            TagOptions(j) = 0
            Exit For

           Else

            TagOptions(j) = j

        End If
    
    Next b

    sh.Cells(j, 8) = TagOptions(j)
Next j

Quicksort TagOptions, LBound(TagOptions), UBound(TagOptions)

For f = LBound(TagOptions) To UBound(TagOptions)

sh.Cells(f, 9) = TagOptions(f)

Next f

**TagOptions2 = Filter(TagOptions, "0", False, vbDatabaseCompare)**

Me.ComboBox1.List = TagOptions


End Sub

Thnak you in advance for any help.

Why don't you use a Collection or arraylist or new Array to store the non zero's only? — EvR, Oct 13 '20 at 12:09
@JackDenton - Posted a late answer to your question using `FilterXML()`; feel free to accept by ticking the hollow green checkmark if helpful. - *Btw are you using Access, as your `Filter()` code (see remarks in my post, too) includes a vbDatabaseCompare argument value?* — T.M., Oct 17 '20 at 19:38

score 0 · Accepted Answer · answered Oct 17 '20 at 19:33

tl;dr entire code, just note that VBA's Filter() function applied on a "flat" 1-dim array only executes a partial character search finding "0" also in strings like e.g. "10" or "205", what definitely isn't what you want to do :-;

Btw, if your initial array is a 2-dim array, there are number of answers at SO how to slice data from a 2-dim array and transpose or double transpose them to a 1-dim array needed as starting point.

Solving the actual core question how to filter out zero-digits

To succeed in filtering out zeros in a 1-dim array, simply use the following function via the Worksheetfunction FilterXML (available since vers. 2013+):

     tagOptions = WorksheetFunction.FilterXML("<t><s>" & _
                  Join(tagOptions, "</s><s>") & "</s></t>", _
                  "//s[not(.='0')]")

resulting in a 1-based 2-dim array.

If you prefer, however to get a resulting 1-dim array instead, simply transpose it via tagOptions = Application.Transpose(tagOptions) or tagOptions = WorkSheetFunction.Transpose(tagOptions).

You can find an excellent overview at Extract substrings ... from FilterXML

Filtering out Numbers from Array

1 Answers1