Excel VBA Array Delete: How to Remove Items Safely

In Excel VBA, use Erase to clear an entire array; it does not remove just one element. To delete one item, shift the later elements over it, then resize a dynamic array with ReDim Preserve. If the array is fixed-size or multidimensional, rebuild it instead. Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z.

What deleting an array item means

A VBA array is a block of indexed values, not a worksheet range. Its elements have positions determined by its lower and upper bounds. For example, an array whose bounds are 0 and 3 has four elements: positions 0, 1, 2, and 3. VBA has no general command that removes an arbitrary element and automatically closes the gap. You need to decide what should happen to the remaining values.

For a one-dimensional list, the usual approach is to move every value after the deleted position one slot toward the beginning. Then reduce the array’s upper bound so the now-unused final slot is no longer part of the array. If you instead want to keep the array’s size, shift the values and set the last slot to an empty or default value. That leaves a placeholder; it is not the same as shrinking the array.

This distinction matters because a deletion can mean different things: emptying every value, removing a particular value, removing a position, or removing a row from data. Choose the operation based on the result you need, rather than treating Erase as a universal delete command.

Erase an entire array or reset its values

Erase acts differently depending on how the array was declared. For a dynamic array, such as one declared with empty parentheses, Erase releases its allocated storage. The array must be allocated again with ReDim before you use its elements. For a fixed-size array, Erase resets each element to its default value but leaves the array’s declared bounds in place.

A dynamic array declared as Dim names() As String is unallocated until it is sized. After Erase names, allocate it again before assigning to an element.
A fixed array declared as Dim scores(1 To 5) As Long still has five positions after Erase scores; their values are reset to zero.
For a fixed array of strings, the reset values are empty strings. Object variables are set to Nothing.

Use Erase when the goal is to clear or release the whole array. Do not use it to remove one item from a list: it does not accept an index, and it does not close a gap between elements.

Remove one item from a dynamic one-dimensional array

The following example uses a zero-based dynamic array of strings and a separate item count. The helper shifts values after the requested index, reduces the count, and resizes the array. It handles the last item specially because an array with no elements is represented here by erasing it rather than trying to create a zero-length array.

Before running this macro, save a copy of the workbook. The example prints the remaining items to the Immediate window in the Visual Basic Editor; it does not change worksheet cells.

Option Explicit

Sub RemoveOneItemExample()
    Dim items() As String
    Dim itemCount As Long
    Dim i As Long

    ReDim items(0 To 3)
    items(0) = "apple"
    items(1) = "pear"
    items(2) = "plum"
    items(3) = "orange"
    itemCount = 4

    RemoveStringAt items, itemCount, 1

    For i = 0 To itemCount - 1
        Debug.Print items(i)
    Next i
End Sub

Private Sub RemoveStringAt(ByRef items() As String, _
                           ByRef itemCount As Long, _
                           ByVal deleteIndex As Long)
    Dim i As Long

    If itemCount <= 0 Then Exit Sub
    If deleteIndex < 0 Or deleteIndex >= itemCount Then Exit Sub

    For i = deleteIndex To itemCount - 2
        items(i) = items(i + 1)
    Next i

    itemCount = itemCount - 1
    If itemCount = 0 Then
        Erase items
    Else
        ReDim Preserve items(0 To itemCount - 1)
    End If
End Sub

After the call, the array contains apple, plum, and orange, in that order. The deleted position was 1, so pear was overwritten by plum and the final value was then moved into the next position. The array is resized from four elements to three. The helper assumes that the array is zero-based and that itemCount matches its current number of elements. If you use a different lower bound, adapt the index checks, loop, and resize bounds together.

Delete by matching a value

Often you know the value to remove rather than its index. Search the array first, then pass the matching position to a deletion routine. A search should also define what to do if there are duplicates: remove the first match, remove every match, or report that more than one item was found. Those are different requirements.

For example, with the items array above and a target of pear, loop from the lower bound to the upper bound and compare each element with the target. When a match is found, call RemoveStringAt items, itemCount, i and stop if only the first match should be removed. To make text matching explicitly case-insensitive, use StrComp(items(i), target, vbTextCompare) = 0 as the comparison. Using vbBinaryCompare instead makes the comparison case-sensitive. This avoids relying on the module’s Option Compare setting.

When removing every matching item, be careful not to skip an element after shifting. If a match is deleted at index i, the next value moves into i; test that same position again rather than always advancing the loop. One reliable method is to search from the end toward the beginning, deleting each match as you find it. Deleting later positions does not change the indexes of earlier values.

Fixed-size and multidimensional arrays

A fixed-size array cannot be shrunk with ReDim Preserve. You can shift values and leave the last element empty or set to a suitable default, but the declared bounds remain. If the array must genuinely become shorter, copy the values you want to keep into a new dynamic array and use that array in place of the fixed one.

Multidimensional arrays need extra care. VBA’s ReDim Preserve can change only the last dimension while preserving existing values; it cannot directly remove a row by shrinking the first dimension. For a two-dimensional array, removing a row usually means creating a new array with one fewer row and copying across every column except the row being removed. Removing a column similarly means copying the other columns into a replacement array with the intended dimensions.

Arrays loaded from worksheet ranges are commonly two-dimensional, even when the range has just one row or column. A range’s values can therefore need different handling from a hand-built one-dimensional list. A single-cell range is a notable edge case: its Value or Value2 result is a scalar rather than a two-dimensional array. Check the shape of the data before applying array bounds or copy loops, especially when the requested range can vary in size.

Choose an array, collection, or dictionary

Arrays are useful when you need compact, indexed data and already know the size or can manage resizing. They are also convenient for reading a block of worksheet values into memory, processing it, and writing results back. Their main drawback for deletion is that resizing and maintaining indexes are your responsibility.

If your code frequently adds and removes individual values, a Collection may be easier to manage. It supports operations such as Remove by position or key, though it is not a drop-in replacement for every typed array operation. A Scripting.Dictionary is useful when each item has a unique key and you need to look up or remove items by that key. Consider platform and reference requirements when choosing libraries or objects, and check whether the data’s ordering and duplicate behavior fit your needs.

Choose an array for indexed values, bulk processing, or data with a predictable size.
Consider a collection when you need a list that grows and shrinks through individual additions and removals.
Consider a dictionary when unique keys and fast key-based lookup are central to the task.
Use a worksheet range when the data should remain visible and editable in the workbook, rather than treating worksheet rows as an in-memory array.

Common errors and safe handling

An invalid index is a frequent source of errors. Before accessing an element, make sure the array is allocated and the requested index lies between LBound(array) and UBound(array). Calling these bound functions on an unallocated dynamic array can itself raise an error, so track whether the array has been initialized or handle that state before checking bounds.

Another common mistake is changing the item count but not resizing the array, or resizing it while leaving a stale count. Keep those two pieces of information synchronized. When a routine expects a zero-based array, document that assumption or write it to use LBound consistently. Also remember that ReDim Preserve preserves values only when resizing an allowed dimension; it cannot reshape an array arbitrarily.

Finally, deleting from an array in a loop can change the positions that later iterations will see. Search backwards when removing multiple matching items, or control the loop index so the shifted value is checked. For macros that alter important data, work on a copy first and test with a small sample. VBA macros cannot be reversed with Excel’s Undo command.

Array deletion versus deleting worksheet cells

Deleting an item from an array does not delete a worksheet cell, row, or column. It changes data held in VBA memory. If the goal is to remove a worksheet row, use a worksheet operation on the relevant range; if the goal is to remove a value from an in-memory array, use array logic. Confusing the two can lead to a macro that changes the wrong thing or leaves the sheet unchanged when you expected it to update.

A common workflow is to read a range into an array, filter or remove values in memory, and write the result to a destination range. In that case, deleting an array item alone still does not update the worksheet. Your code must also write the shortened data back, taking account of the destination range’s dimensions and any remaining rows. Keeping the transformation separate from the worksheet write makes it easier to inspect the result before overwriting source data.

Frequently Asked Questions

Can I use Erase to delete one element?
No. Erase acts on the entire array. To remove one element from a one-dimensional dynamic array, shift the following values and resize the array, or rebuild it while omitting the unwanted element.

Does ReDim Preserve work on every dimension?
No. It can preserve existing values while changing only the last dimension. To remove a row from a two-dimensional array, create a replacement array and copy the rows you want to keep.

Can I make a dynamic array have zero elements with ReDim?
Do not rely on a zero-length ReDim as the empty-array solution. In a deletion routine, handle the last item separately with Erase, then allocate the array again if you later add items.

How do I remove all duplicate values?
Search from the highest index toward the lowest and delete each match, or build a new array that includes only the values you want to retain. A backwards search avoids overlooking a value that shifts into the position just deleted.

Will deleting an array element update the cells it came from?
No. Once values are in a VBA array, changing that array changes only the in-memory data. Write the revised array to a worksheet range separately if the cells should reflect the change.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.