VBA: How to dynamically create a multi-dimensional array from a range with varying row lengths?
I'm working on an Excel VBA project where I need to extract data from a specified range and store it in a multi-dimensional array. The scenario I'm working with is that the number of rows in the range can vary based on user inputs, and each row may contain a different number of columns depending on the data. I've tried using a dynamic approach to set the size of the array, but I'm working with issues when trying to access elements. Here’s what I have so far: ```vba Dim dataRange As Range Dim dataArray() As Variant Dim rowCount As Long, colCount As Long Set dataRange = ThisWorkbook.Sheets("Sheet1").Range("A1:C5") ' Example range rowCount = dataRange.Rows.Count ' Initialize the array ReDim dataArray(1 To rowCount) As Variant Dim i As Long For i = 1 To rowCount colCount = Application.WorksheetFunction.CountA(dataRange.Rows(i)) ReDim dataArray(i)(1 To colCount) As Variant dataArray(i) = Application.Transpose(Application.Transpose(dataRange.Rows(i))) ' Attempt to assign row values Next i ``` When I run this code, I get a "Subscript out of range" behavior on the line where I try to assign values to `dataArray(i)`. I suspect it might be due to the way I'm trying to dynamically resize the inner array or the way I'm referencing it. I've also considered using a collection instead of an array, but I need to stick with multi-dimensional arrays for compatibility with other parts of my code. Any insights on how to properly create and assign values to a dynamic multi-dimensional array in this scenario? Thanks in advance for your help!