Removing elements in an array if the element is a specific VBA value

Question

I have a global array prLst() , which can have a variable length. It accepts numbers as strings "1" to Ubound(prLst) . However, when the user enters "0" , I want to remove this item from the list. For this, I wrote the following code:

 count2 = 0 eachHdr = 1 totHead = UBound(prLst) Do If prLst(eachHdr) = "0" Then prLst(eachHdr).Delete count2 = count2 + 1 End If keepTrack = totHead - count2 'MsgBox "prLst = " & prLst(eachHdr) eachHdr = eachHdr + 1 Loop Until eachHdr > keepTrack 

This does not work. How to effectively remove elements in the prLst array if the element is "0" ?

---

NOTE. . This is part of a larger program, the description of which can be found here: Sorting Excel VBA Macro row groups

Answer 1

An array is a structure with a certain size. You can use dynamic arrays in vba, which you can compress or grow with ReDim, but you cannot delete elements in the middle. It’s not clear from your example how your array works, or how you determine the index position (eachHdr), but you basically have 3 options

(A) Write a user-defined function 'delete' for your array like (untested)

 Public Sub DeleteElementAt(Byval index As Integer, Byref prLst as Variant) Dim i As Integer ' Move all element back one position For i = index + 1 To UBound(prLst) prLst(i - 1) = prLst(i) Next ' Shrink the array by one, removing the last one ReDim Preserve prLst(Len(prLst) - 1) End Sub 

(B) Just set the value of 'dummy' as the value instead of actually deleting the item

 If prLst(eachHdr) = "0" Then prLst(eachHdr) = "n/a" End If 

(C) Stop using the array and change it to VBA.Collection. A collection is a (unique) key / value pair structure where you can freely add or remove elements from

 Dim prLst As New Collection 

Answer 2

 Sub DelEle(Ary, SameTypeTemp, Index As Integer) '<<<<<<<<< pass only not fixed sized array (i don't know how to declare same type temp array in proceder) Dim I As Integer, II As Integer II = -1 If Index < LBound(Ary) And Index > UBound(Ary) Then MsgBox "Error.........." For I = 0 To UBound(Ary) If I <> Index Then II = II + 1 ReDim Preserve SameTypeTemp(II) SameTypeTemp(II) = Ary(I) End If Next I ReDim Ary(UBound(SameTypeTemp)) Ary = SameTypeTemp Erase SameTypeTemp End Sub Sub Test() Dim a() As Integer, b() As Integer ReDim a(3) Debug.Print "InputData:" For I = 0 To UBound(a) a(I) = I Debug.Print " " & a(I) Next DelEle a, b, 1 Debug.Print "Result:" For I = 0 To UBound(a) Debug.Print " " & a(I) Next End Sub 

Answer 3

I am new to vba and excel - I only did this for about 3 months - I thought I would share my array deduplication method here, as this post seems appropriate to him:

This code, if it is part of a larger application that analyzes data, Pipes are listed in a sheet with a number in the format xxxx.1, xxxx.2, yyyy.1, yyyy.2 ..... so this is why all the string manipulations exist . basically it only collects the pipe number only once, and not the .2 or .1 part.

  With wbPreviousSummary.Sheets(1) ' here, we will write the edited pipe numbers to a collection - then pass the collection to an array Dim PipeDict As New Dictionary Dim TempArray As Variant TempArray = .Range(.Cells(3, 2), .Cells(3, 2).End(xlDown)).Value For ele = LBound(TempArray, 1) To UBound(TempArray, 1) If Not PipeDict.Exists(Left(TempArray(ele, 1), Len(TempArray(ele, 1) - 2))) Then PipeDict.Add Key:=Left(TempArray(ele, 1), Len(TempArray(ele, 1) - 2)), _ Item:=Left(TempArray(ele, 1), Len(TempArray(ele, 1) - 2)) End If Next ele TempArray = PipeDict.Items For ele = LBound(TempArray) To UBound(TempArray) MsgBox TempArray(ele) Next ele End With wbPreviousSummary.Close SaveChanges:=False Set wbPreviousSummary = Nothing 'done early so we dont have the information loaded in memory 

Using a bunch of message boxes to debug atm - im sure you will change it to suit your own work.

I hope people find this useful, Regards Joe

Answer 4

Removing elements in an array if the element is a specific VBA value

to remove elements in an array with a certain condition, you can make the code as follows

 For i = LBound(ArrValue, 2) To UBound(ArrValue, 2) If [Certain condition] Then ArrValue(1, i) = "-----------------------" End If Next i StrTransfer = Replace(Replace(Replace(join(Application.Index(ArrValue(), 1, 0), ","), ",-----------------------,", ",", , , vbBinaryCompare), "-----------------------,", "", , , vbBinaryCompare), ",-----------------------", "", , , vbBinaryCompare) ResultArray = join( Strtransfer, ",") 

I often manipulate a 1D array with Join / Split but if you need to delete a specific value in Multi Dimension, I suggest you change this array to a 1D array this way

 strTransfer = Replace(Replace(Replace(Replace(Names.Add("A", MultiDimensionArray), Chr(34), ""), "={", ""), "}", ""), ";", ",") 'somecode to edit Array like 1st code on top of this comment 'then loop through this strTransfer to get right value in right dimension 'with split function. 

Answer 5

here is sample code using the CopyMemory function to complete the job.

It is supposedly "much faster" (depending on the size and type of the array ...).

I am not an author, but I tested it:

 Sub RemoveArrayElement_Str(ByRef AryVar() As String, ByVal RemoveWhich As Long) '// The size of the array elements '// In the case of string arrays, they are '// simply 32 bit pointers to BSTR's. Dim byteLen As Byte '// String pointers are 4 bytes byteLen = 4 '// The copymemory operation is not necessary unless '// we are working with an array element that is not '// at the end of the array If RemoveWhich < UBound(AryVar) Then '// Copy the block of string pointers starting at ' the position after the '// removed item back one spot. CopyMemory ByVal VarPtr(AryVar(RemoveWhich)), ByVal _ VarPtr(AryVar(RemoveWhich + 1)), (byteLen) * _ (UBound(AryVar) - RemoveWhich) End If '// If we are removing the last array element '// just deinitialize the array '// otherwise chop the array down by one. If UBound(AryVar) = LBound(AryVar) Then Erase AryVar Else ReDim Preserve AryVar(LBound(AryVar) To UBound(AryVar) - 1) End If End Sub 

Answer 6

I know this is old, but here is the solution I came up with when I didn’t like the ones I found.

-Loop through an array (Variant), adding each element and some separator to the string, if it does not match the one you want to remove -Then split the string on the separator

 tmpString="" For Each arrElem in GlobalArray If CStr(arrElem) = "removeThis" Then GoTo SkipElem Else tmpString =tmpString & ":-:" & CStr(arrElem) End If SkipElem: Next GlobalArray = Split(tmpString, ":-:") 

Obviously, the use of strings creates some restrictions, for example, you need to be sure of the information already in the array, and as if this code makes the first element of the array empty, but it does what I need, and with a little more work, it can be more versatile.

Answer 7

When creating an array, why not just skip 0 and save yourself some time to worry about them later? As mentioned above, arrays are not suitable for deletion.