Add an item to an existing array

[Rudi]

Hi,

I have an array variable called: arrVal()
It contains the words 1 - 10 - {"One","Two","Three","......","Ten"}
I successfully added an extra value to it, using:

Code: Select all

    arrVals(UBound(arrVals)) = "Eleven"
    ReDim Preserve arrVals(1 To UBound(arrVals) + 1)

But need some insight into the code:
-- arrVals(UBound(arrVals)) = "Eleven" (I would have thought this would overwrite the last value "Ten"??)
-- ReDim Preserve arrVals(1 To UBound(arrVals) + 1) (And only now do we add a new element to the array??)

Just some clarity for understandng, please.
TX

[HansV]

That looks strange. Can you post the complete code?

[Rudi]

Hi,

It's the code that you helped me on a few days ago. I just made small changes and added this to prevent blank cells from also being filtered...
Attached is the sample file if you want to test it.

Code: Select all

Private Sub cmdFilter_Click()
    Dim cChk As Control, arrVals, i As Long
    Dim X, objDict As Scripting.Dictionary, lngRow As Long

    Application.ScreenUpdating = False

    'Clear filters
    On Error Resume Next
    ActiveSheet.ShowAllData
    On Error GoTo 0

    'Get unique list of values in the filter column (Col. "B")
    Set objDict = New Scripting.Dictionary
    X = Application.Transpose(Range("B13", Cells(Rows.Count, "B").End(xlUp)))
    For lngRow = 1 To UBound(X, 1)
        objDict(X(lngRow)) = 1
    Next lngRow

    For Each cChk In Me.grpFilter.Controls
        If TypeName(cChk) = "CheckBox" Then
            If cChk.Value = True Then
                objDict.Remove cChk.Tag
            End If
        End If
    Next cChk

    ReDim arrVals(objDict.Count - 1)
    For i = 0 To objDict.Count - 1
        arrVals(i) = objDict.Keys(i)
    Next i
    arrVals(UBound(arrVals)) = "="
    ReDim Preserve arrVals(1 To UBound(arrVals) + 1)
    On Error Resume Next
    ActiveSheet.Range("A12").CurrentRegion.AutoFilter _
        Field:=2, Criteria1:=arrVals, Operator:=xlFilterValues
    Unload Me
    Application.ScreenUpdating = False
End Sub

[HansV]

The code does overwrite the last element of the array with "=", and if you check carefully, you'll see that the code doesn't do what you want: R4 is always excluded. It was the last element in the array, and it is overwritten with "=".

Change the two lines to

Code: Select all

    ReDim Preserve arrVals(UBound(arrVals) + 1)
    arrVals(UBound(arrVals)) = "="

[Rudi]

Logic rules...

TX for confirming that.
Appreciated.