How to clear the entire array?

arrays excel vba
Alex Gordon picture Alex Gordon · Jun 10, 2010 · Viewed 352.8k times · Source

I have an array like this:

Dim aFirstArray() As Variant

How do I clear the entire array? What about a collection?

Answer

Sarfraz picture Sarfraz · Jun 10, 2010

You can either use the Erase or ReDim statements to clear the array. Examples of each are shown in the MSDN documentation. For example:

Dim threeDimArray(9, 9, 9), twoDimArray(9, 9) As Integer
Erase threeDimArray, twoDimArray
ReDim threeDimArray(4, 4, 9)

To remove a collection, you iterate over its items and use the Remove method:

For i = 1 to MyCollection.Count
  MyCollection.Remove 1 ' Remove first item
Next i

Related questions

Creating an Array from a Range in VBA

I'm having a seemingly basic problem but can't find any resources addressing it. Simply put, I just want to load the contents of a Range of cells (all one column) into an Array. I am able to accomplish this by …

Read More
Excel VBA: Variants in Array Variables

A question on variants. Im aware that variants in Excel vba are both the default data type and also inefficient (from the viewpoint of overuse in large apps). However, I regularly use them for storing data in arrays that have …

Read More
How do I slice an array in Excel VBA?

What function can I use in Excel VBA to slice an array?

Read More