How to put all values in the first row into an array?

Question

I am trying to set my array equal to all column headers of tables in a worksheet.

If I were doing this in Excel, I would click on A1 and do Control+Shift+Right Arrow. I've found some 15 year old code attempting to simulate this, shown below, but it doesn't recognize the last Column.

Sub Ls_List_Click()
'variables used in for each loops
Dim Column_Array() As Variant
Dim EndRange As Range

ThisWorkbook.Worksheets(Me.Ls_List.Value).Select

Range("A1", Range("A1").End(xlRight)).Select

EndRange = Range("A1").End(xlRight).Offset(2, 0)

Range(EndRange, EndRange.End(xlRight)).Select
EndRange = EndRange.End(xlRight).Offset(2, 0)

Column_Array() = EndRange

'... couple of for each loops

End Sub

I get error 1004 on the following line:

Range("A1", Range("A1").End(xlRight)).Select

What are the `.select` statements even being used for? there is no reference to `.selection`, and each `set endrange` refinds those points. Seems like those could be removed completely and this would still work. And `endrange` is just finding the last column then offsetting two rows down; is this the goal? — Cyril, Oct 09 '19 at 16:30
You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Oct 10 '19 at 05:55

score 0 · Answer 1 · answered Oct 09 '19 at 16:24

0

If you replace xlRight with xlToRight you should be good

answered Oct 09 '19 at 16:24

Sam

5,424
1
18
33

Cyril · Answer 2 · 2019-10-09T16:59:10.270

I believe you're looking for something more like:

Sub Ls_List_Click()
'variables used in for each loops
Dim Column_Array() As Variant

Column_Array() = Range(Cells(1,1),Cells(1, Cells(1, 1).End(xlToRight).Column)).value

'... couple of for each loops

End Sub

There didn't appear to be rhyme or reason for the .select components beyond stepping through to see where things were (nice for testing... i guess?).

Just one line of code gets your array generated for all the contiguous headers in your first row.

Edit:

Checking back, because EndRange is used for looping, you may want to write:

Sub Ls_List_Click()
'variables used in for each loops
Dim Column_Array() As Variant, EndRange as Long 'reference to column number, not a range, so watch referencing

EndRange = Cells(1, 1).End(xlToRight).Column
Column_Array() = Range(Cells(1,1),Cells(1, EndRange)).value

'... couple of for each loops

End Sub

score 0 · Answer 3 · answered Oct 10 '19 at 05:24

Two main issues with your code:

You misspelled xlRight - the correct value is xlToRight. That's why it is recommended to use Option Explicit, so such errors are detected at compile time.
You shouldn't use Select - what for? If you want to reference particular cells/range, just use Cells(row, column) (row and column are integers) or Range("A1:B3") (just example).

Having said that, to get values into an array, you should use:

firstRowArray = Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Value2

Expalnation:

I used Range overload, accepting two cells as top-left and bottom-right cells to define the range. Top left is Cell(1, 1), i.e. A1 and bottom left is Cells(1, 1).End(xlToRight), which you are already familiar with :)

Then I use Value2 property, which return array of values (you could use Value but it's slower).

Note that, the result will be two-diensional array with number of rows and number of columns same as in the range.

How to put all values in the first row into an array?

3 Answers3