vba Delete an item in an array 1

[walejo89]

I need to be able to delete a item in array and move all other items to the beggining
wilfer
Thanks

 

[PHV]

You may consider using a Collection or a Scripting.Dictionary object instead.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VBslammer]

You'll have to create a new array no matter how it's done, but you can automate it somewhat with a function:

Public Function ArrayDelete(ByRef oArray As Variant, ByVal indexToDelete As Long) As Variant
On Error GoTo ErrHandler
  Dim index As Long
  Dim tmpArray As Variant
  
  If indexToDelete < LBound(oArray) Or indexToDelete > UBound(oArray) Then
    Err.Raise 9
  End If
  
  ReDim tmpArray(LBound(oArray) To (UBound(oArray) - 1))
  
  For index = indexToDelete To (UBound(oArray) - 1)
    oArray(index) = oArray(index + 1)
  Next index
  
  For index = LBound(tmpArray) To UBound(tmpArray)
    tmpArray(index) = oArray(index)
  Next index
  
  ArrayDelete = tmpArray
  
ExitHere:
  Exit Function
ErrHandler:
  MsgBox Err & "-" & Err.Description
  ArrayDelete = oArray
  Resume ExitHere
End Function

The calling code has to assign the result to a variant (a VB limitation) which will become the new array. Here's an example test:

Sub TestArrayDelete(ByVal index As Long)
  Dim oldArray(1 To 8) As String
  Dim newArray As Variant
  Dim l As Long
  
  oldArray(1) = "1-Tek"
  oldArray(2) = "2-Tips"
  oldArray(3) = "3-Unites"
  oldArray(4) = "4-People"
  oldArray(5) = "5-From"
  oldArray(6) = "6-Around"
  oldArray(7) = "7-The"
  oldArray(8) = "8-Globe"
  
  newArray = ArrayDelete(oldArray, index)
  Erase oldArray
  
  For l = LBound(newArray) To UBound(newArray)
    Debug.Print newArray(l)
  Next l
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob