Why is this creating a two dimensional array - Excel VBA

Question

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?

FYI: this is a great resource for understanding arrays and ranges. — BigBen
– BigBen, Commented Sep 12, 2019 at 20:21

Scott Craner · Accepted Answer · 2019-09-12 21:09:04Z

3

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.

edited Sep 12, 2019 at 21:09

answered Sep 12, 2019 at 20:25

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

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

4 Comments

Country_Gravy Over a year ago

Transpose worked perfectly for what I am doing. Thanks!

Mathieu Guindon Over a year ago

Careful: these unqualified Cells calls inside a qualified Worksheet.Range call cause error 1004 every day.

Scott Craner Over a year ago

@MathieuGuindon focused on the other issue and missed that. It has been fixed.

Scott Craner Over a year ago

@Country_Gravy Please see Mathieu's comment and my edit to fix. Also if this worked for you, please consider marking as correct by clicking the check mark by the answer.

BruceWayne · Accepted Answer · 2019-09-12 20:53:43Z

1

You could loop through the range¹:

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}

edited Sep 12, 2019 at 20:53

answered Sep 12, 2019 at 20:27

BruceWayne

23.5k16 gold badges75 silver badges126 bronze badges

Collectives™ on Stack Overflow

Why is this creating a two dimensional array - Excel VBA

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related