Home > OS >  Deleting an item from middle of an array in vba
Deleting an item from middle of an array in vba

Time:11-17

Deleting an item from middle of an array in vba

I am looping through two arrays to look for which item in Arr1 is equal to Arr2. Then I want to delete that item from Arr1. At the end I will add that Arr1 to Arr2. So, it's somehow detecting if Arr1 has new item that is not in Arr2, and add that item to Arr2

Public Sub cmpArr()

For i =LBound(Arr1) To UBound(Arr2)
    For j = LBound(Arr2) To UBound(Arr2)
         If Arr1(i) = Arr2(j) Then
            'Arr1(i) delete
         End If
    Next j
Next i

End Sub

User braX suggested to use dictionaries. On his advice, I changed using arrays to using dictionaries. Here's code

Public Sub comp_2Dictnries()
   For i =0 To dict1.Count -1 
      For j=0 To dict2.Count -1
          On Error Resume Next
          If dict1.Items()(i) =dict2.Items()(j) Then
             dict1.Remove dict1.Keys()(i)
          End If
      Next j
   Next i
End Sub

CodePudding user response:

Please, try the next way:

Sub replaceArrayElem()
  Dim arr1, arr2, arr3, mtch, i As Long, j As Long
  arr1 = Array("a", "c", "d", "jj", "t", "www")
  arr2 = Array("m", "r", "o", "c", "uu", "n", "d")
  For i = LBound(arr2) To UBound(arr2)
        mtch = Application.match(arr2(i), arr1, 0)
        If IsNumeric(mtch) Then 'if a match exists
            arr1(mtch - 1) = "#$@!": arr1 = filter(arr1, "#$@!", False) 'filter eliminate a specific element
        End If
  Next i
  Debug.Print Join(arr1, ",") ' just to visually see how arr1 remained
  arr3 = arr2: ReDim Preserve arr3(UBound(arr2)   UBound(arr1)   1)
  For i = UBound(arr2)   1 To UBound(arr3)
       arr3(i) = arr1(j): j = j   1
  Next i
  Debug.Print Join(arr3, ",") 'the returned array...
End Sub

Please, send some feedback after testing it.

And a version using a Scripting.Dictionary (if you have the arrays, or like working with them):

Sub replaceArrayElemDict()
  Dim arr1, arr2, arr3, mtch, i As Long, j As Long
  Dim dict As Object
  
  arr1 = Array("a", "c", "d", "jj", "t", "www")
  arr2 = Array("m", "r", "o", "c", "uu", "n", "d")
  
  Set dict = CreateObject("Scripting.Dictionary")
  For i = LBound(arr2) To UBound(arr2)
        dict(arr2(i)) = vbNullString
  Next i
  For i = LBound(arr1) To UBound(arr1)
        dict(arr1(i)) = vbNullString 'it creates a NEW dictionary key only if it does not exist...
  Next i
  
  Debug.Print Join(dict.Keys, ",") 'the returned array...
End Sub
  • Related