Get Spreadsheet List into VBA Array

[mrmmickle1]

If I have a list like this:

Excel 2012
	A
1	Company Names
2	Company A
3	Company B
4	Company C
5	Company D
Sheet1

How would I get these into an array (Excluding the Header)? I am familiar with hard coding arrays like this:

 CompArray = Array("Company A", "Company B", "Company C", "Company D") [COLOR=#008000]'Define Name Array[/COLOR]

How can I do the same thing but make the list dynamic. So the array will consist of Company's A-D now. However, if I added for example a few more:

Excel 2012
	A
1	Company Names
2	Company A
3	Company B
4	Company C
5	Company D
6	Company E
7	Company F
8	Company G
Sheet1

It would now encompass these additional Companies.... (Companies A-G)

Any help would be much appreciated. Thanks.

 

[NeonRedSharpie]

Sub addArray()

    Dim compNames() As Variant


    Dim rowStart As Integer
    Dim rowEnd As Long
    
    rowStart = 2
    rowEnd = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row


    compNames = Range(Cells(rowStart, 1), Cells(rowEnd, 1))


    For x = 1 To rowEnd - 1
        MsgBox compNames(x, 1)
        ' THIS IS JUST TO SHOW AND TEST
    Next x




End Sub

 

[Andrew Poulsom]

Maybe try:

CompArray = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Value

 

[mrmmickle1]

NeonRedSharpie/Andrew Poulsom,

Thanks so much. I appreciate the help. This will come in handy for many tasks.

 

[RoryA]

Make sure you note that the array is now 2 dimensional so you have to use comparray(x, 1) when referring to elements of it, not just comparray(x)

 

[mrmmickle1]

RoryA,

Thanks for the tip. I probably would have overlooked that.