Items - Add / Remove

How you add and remove items is exactly the same for both fixed and dynamic arrays.

Adding

Once the array has been declared it can be populated.

Dim myArray(3) As Long 
myArray(0) = 50 
myArray(1) = 100 
myArray(2) = 150 
myArray(3) = 200 

Removing

The only way to remove an item from an array is to construct a new array.
Constructing a new array every time though is not very efficient.
The following function can be used to remove individual items from an array.

Public Function Array_RemoveItem(ByVal myArray As Variant, _ 
                                 ByVal iIndexNo As Integer) As Variant 
Dim i As Integer 

    For i = iIndexNo + 1 To UBound(myArray) 
        myArray(i - 1) = myArray(i) 
    Next i 
    ReDim Preserve myArray(UBound(myArray) - 1) 
    Array_RemoveItem = myArray 
End Function 

Public Sub Testing() 
Dim myNewArray As Variant 
Dim myArray() As String 

   ReDim myArray(4) 
   myArray(0) = "one" 
   myArray(1) = "two" 
   myArray(2) = "three" 
   myArray(3) = "four" 
   myArray(4) = "five" 

   myNewArray = Array_RemoveItem(myArray, 2) 
   Stop 
End Sub 

If you want to remove duplicates from an array, use the Array_RemoveDuplicatesSingle code snippet.

Removing All - ERASE Statement

The ERASE statement can be used to remove all of the items.
This can be used with both Fixed Arrays and Dynamic Arrays.
This statement will reset all the elements to their default values.

Dim myArray(3) As String     'This is a FIXED array  
Debug.Print myArray(0)       'Default value is ""  
myArray(0) = "one" 
myArray(1) = "two" 
myArray(2) = "three" 
myArray(3) = "four" 

Debug.Print myArray(0)       '= "one"  
Erase myArray 
Debug.Print myArray(0)       '= "" (resets to default value)  

Removing All - REDIM Statement

The REDIM statement can be used to remove all of the items.
This can only be used with Dynamic Arrays.

Dim myArray() As String      'This is a DYNAMIC array  
ReDim myArray(3) 
myArray(0) = "one" 
myArray(1) = "two" 
myArray(2) = "three" 
myArray(3) = "four" 

Debug.Print myArray(0)       '= "one"  
Erase myArray 
Debug.Print myArray(0)       '= "" (resets to default value)  

It is possible to use the Preserve keyword to remove only some of the values.

Using Dummy Values

Instead of constructing a new array you could just change the value to a dummy value.
This function works with Fixed Arrays and Dynamic Arrays.

Public Function Array_ChangeItem(ByVal myArray As Variant, _ 
                                 ByVal iIndexNo As Integer) As Variant 
    myArray(iIndexNo) = "#ignore#" 
    Array_ChangeItem = myArray 
End Function 

Public Sub Testing() 
Dim myFixedArray(4) As String 

   myFixedArray(0) = "one" 
   myFixedArray(1) = "two" 
   myFixedArray(2) = "three" 
   myFixedArray(3) = "four" 
   myFixedArray(4) = "five" 

   myNewArray = Array_ChangeItem(myFixedArray, 2) 'myNewArray = Array_RemoveItem(myArray, 2)  
   Stop 
End Sub 

Collection Object

VBA does include a built-in Collection Object that allows items to be easily removed.