Question posted 2010 · +29 upvotes
I have an array like this:
Dim aFirstArray() As Variant
How do I clear the entire array?
What about a collection?
Accepted answer +58 upvotes
You can either use the Erase or ReDim statements to clear the array:
Dim threeDimArray(9, 9, 9), twoDimArray(9, 9) As Integer
Erase threeDimArray, twoDimArray
ReDim threeDimArray(4, 4, 9)
See the different usage of each method here.
Update
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
2 code variants in this answer
- Variant 1 — 3 lines, starts with
Dim threeDimArray(9, 9, 9), twoDimArray(9, 9) As Integer - Variant 2 — 3 lines, starts with
For i = 1 to MyCollection.Count
Top excel-vba Q&A (6)
- How to change Format of a Cell to Text using VBA +55 (2011)
- Download attachment from Outlook and Open in Excel +43 (2012)
- Can a VBA function in Excel return a range? +36 (2009)
- 2 Dimensional array from range +34 (2013)
- Hiding an Excel worksheet with VBA +33 (2009)
- How do I slice an array in Excel VBA? +31 (2008)
excel-vba solutions on this site
— top 1%.