Dynamicall Add Arrays to create a 2D Array

Question

I have looked at just about every post I could find on this topic, but I can't seem to get anything to work.

I want to be able to use a 2D Array for my own sake, but I think my best bet is to just make one huge 1D Array.

Sub GatherData()
Dim width As Integer: width = 22

Dim data() As Variant
ReDim roadway(width) As Variant

Dim str As Variant
Dim arrayWidth As Integer

With ThisWorkbook.Worksheets("List of Roads").Range("A1")

    For r = 0 To 400 '400 is an arbitrary number
        If Not IsEmpty(.Offset(r, 0).value) Then
            If IsStreet(UCase(.Offset(r, 0).value), UCase(.Offset(r, 1).value)) Then
                For c = 0 To width - 1
                    roadway(c) = .Offset(r, c).value
                Next c
            End If

            'add roadway array to the end of data()

        End If
    Next r
End With

End Sub

If I had my way this 2D-Array would have an unknown number of rows with each row containing 22 columns. I just can't wrap my head around how I would dynamically add arrays together.

Do you need a 1D or 2D array in the end? Had to ask since I cannot see a question in.. question. — AntiDrondert
– AntiDrondert, Commented Feb 19, 2018 at 6:43
I don't need a 2D array. Just wanted one. It would make my code look a little cleaner in the end. Plus this would be my first time working with a multi-dim array. — khorn06
– khorn06, Commented Feb 19, 2018 at 18:21
@khorn06, may be you could add some examples about wanted array result — DisplayName
– DisplayName, Commented Feb 19, 2018 at 21:55

Tim Williams · Accepted Answer · 2018-02-19 06:20:45Z

1

Dim data
data = ThisWorkbook.Worksheets("List of Roads").Range("A1").Resize(400, 22).Value

would give you a 2D array (1 to 400, 1 to 22)

answered Feb 19, 2018 at 6:20

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

khorn06 Over a year ago

The 400 is just an arbitrary number.. the actual size the of 2d array I would need for this specific case is 118 rows. In the future that number will change.

Tim Williams Over a year ago

So what exactly will determine the size of the array (# of "rows")?

Collectives™ on Stack Overflow

Dynamicall Add Arrays to create a 2D Array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related