ReDim Preserve “Subscript Out of Range”

by

To summarise the comments above into an answer:

  • You can only redim the last dimension of a multi dimension array when using Preserve.

Therefore in order to resize a multiple dimension array there are a couple of simple options:

  1. If only one dimension needs to be resized flip the loops and logic around so that the dimension to be resized becomes the last dimension
  2. If both dimensions must be resized use either an array of arrays or a collection of arrays and correct the loops as required

More Related Contents:

  1. VBA array sort function?
  2. Excel-VBA – Insert new first column in datafield array without loops or API calls
  3. Creating an Array from a Range in VBA
  4. Expanding column cells for each column cell
  5. How do I slice an array in Excel VBA?
  6. Check if a value is in an array or not with Excel VBA
  7. Matching values in string array
  8. Deleting Elements in an Array if Element is a Certain value VBA
  9. Is it possible to fill an array with row numbers which match a certain criteria without looping?
  10. How to automatically input an array formula as string with more than 255 characters in length into an excel cell using VBA?
  11. Return Index of an Element in an Array Excel VBA
  12. Writing an array to a range. Only getting first value of array
  13. Excel VBA: Range to String Array in 1 step
  14. Faster way of using Sumifs()
  15. How to initialize a multidimensional array variable in vba for excel
  16. VBA using ubound on a multidimensional array
  17. Turn Excel range into VBA string
  18. Get values from union of non-contiguous ranges into array with VBA with a simple command (no loops)
  19. Why am I having issues assigning a Range to an Array of Variants
  20. Subscript out of range error with an array – no idea why?
  21. VBA Excel large data manipulation taking forever
  22. Performance difference between looping range vs looping array
  23. Create Chart from Array data and not range
  24. How do I Merge two Arrays in VBA?
  25. Excel VBA Quickest way to sort an array of numbers in descending order?
  26. Simple VBA array join not working
  27. Excel VBA – How to Redim a 2D array?
  28. ReDim Preserve to a multi-dimensional array in VB6
  29. Dynamically Dimensioning A VBA Array?
  30. VBA – Returning array from Property Get

Leave a Comment