CODEWITHSUNDEEP
arraysexcelvba
Country_Gravy
Country_Gravy

Reputation: 33

Why is this creating a two dimensional array - Excel VBA

Here is the line of code to load my array with names starting on column E and going to the last name in the row determined by a loop earlier. The loop calculated "LastColumn". I wanted to call out that it should be looking at the first sheet, although the button to start this is on that page. Not sure if it's needed.

Dim AssociateNameArray As Variant

AssociateNameArray = Worksheets(1).Range(Cells(1, 5), (Cells(1, LastColumn))).Value

This creates a two dimensional array with (1,1) having the first name, (1,2) the second name, and so on.

How do I modify this so that puts the data within the range to a one dimensional array?

Upvotes: 1

Views: 361

Answers (2)

Scott Craner
Scott Craner

Reputation: 152465

Two ways:

Transpose the array twice

AssociateNameArray = Application.Transpose(Application.Transpose(Worksheets(1).Range(Worksheets(1).Cells(1, 5), Worksheets(1).Cells(1, LastColumn)).Value))

Or loop the 2d Array and put it in a 1d Array:

Dim AssociateNameArray As Variant
AssociateNameArray = Worksheets(1).Range(Worksheets(1).Cells(1, 5), Worksheets(1).Cells(1, LastColumn)).Value

Dim newArr()
ReDim newArr(1 To UBound(AssociateNameArray, 2))

Dim i As Long
For i = 1 To UBound(AssociateNameArray, 2)
    newArr(i) = AssociateNameArray(1, i)
Next i

The transpose has a limit to the number of items allowed, but should be okay for a single row. The second has no such limitations.

one could technically loop the range and skip the 2d array but that would be the slower option as anytime the code breaks the veil to the worksheet it slows down the code.

Upvotes: 3

BruceWayne
BruceWayne

Reputation: 23283

You could loop through the range1:

Sub t()
Dim AssociateNameArray As Variant
Dim rng As Range
Dim lastColumn As Long, i As Long
lastColumn = 10 'for example
Set rng = Worksheets(1).Range(Cells(1, 5), (Cells(1, lastColumn)))

ReDim AssociateNameArray(1 To rng.Cells.Count)

For i = LBound(AssociateNameArray) To UBound(AssociateNameArray)
    AssociateNameArray(i) = rng.Cells(i).Value
Next i
End Sub

Generally, when sending a Range to an array, it creates a two-dimensional array for (row, column).

1: As @ScottCraner notes, this is a slower way to do this. Just wanted to mention for academic purposes

Upvotes: 1

Related Questions