Load a variable range into an array-CodePudding

I want to store a range of variable size in an one-dimensional array. The range starts at A2 and goes to the last row of the same column. My approach looks like that. It's flawed.

Option Explicit

Sub Range_to_Array()

Dim i, j, k As Integer
Dim arr1(), arr2(), arr3(), arr4() As Variant

    With Worksheets("table1")

        arr1() = .Cells(.Range("A2"), .Range("A1").End(xlDown).Row)

    End With

End Sub

CodePudding user response：

Please, try the next way. Application.Transpose transforms a 2D array with a column in a 1D type. No iteration needed:

Sub Array1DFromColumnRange()
 Dim ws As Worksheet, lastR As Long, arr
 
 Set ws = Worksheets("table1")
 lastR = ws.Range("A" & ws.rows.count).End(xlUp).Row
 arr = Application.Transpose(ws.Range("A2:A" & lastR).Value)
    Debug.Print Join(arr, "|") 'just to visually see the result in Immediate Window (Ctrl   G)...
End Sub

CodePudding user response：

Your problem is that your Range-Definition is wrong.

Cells expect 2 parameters (row and column) to address one cell. This is not what you want, and even if, your parameters would be wrong.

What you need in your case is Range.

Now Range can be called either with one or two parameters.
If you call it with one parameter, this defines the whole range.
Examples: Range("A1") or Range("B2:C5") or Range("B:B")
Whats often used in VBA is something like Range("A1:A" & lastRow)

If you call it with two parameters, those two parameters define the first and last cell of the range.
Examples: Range(Range("A1"), Range("C10")) or Range(Cells(1, 1), Cells(10, 3))

I would advice to define an intermediate variable to save the Range - makes it much easier to debug. Also the row number of the last cell should go into an intermediate variable.

In your case you could use one of the following

Dim r As Range, lastRow As Long
' Get the last row in use (usually its better to go up from the end of the sheet)
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

' 2 parameters
Set r = .Range(.Cells(2, 1), .Cells(2, lastRow)) 
' 1 Parameter, last row is concatenated to the range definition
Set r = .Range("A2:A" & lastRow)  
' Use Resize 
Set r = .Range("A2").Resize(lastRow-1, 1)   ' -1 because you start at row 2

arr1 = r.Value

CodePudding user response：

Try this instead.

Sub Range_to_Array()

Dim i As Integer, j As Integer, k As Integer
Dim arr1() As Variant, arr2() As Variant, arr3() As Variant, arr4() As Variant
Dim myRange As Range
    With Worksheets("table1")
        arr1 = .Range(.Range("A2"), .Range("A1").End(xlDown)).Value

    End With
    Debug.Print arr1(1, 1)
End Sub

Also please note that in order to properly declare variables, you need to specify data type for each variable separately.

Dim i, j, k As Integer

actually means

Dim i As Variant, j As Variant, k As Integer