CODEWITHSUNDEEP
arraysexcelvba
kiltro
kiltro

Reputation: 57

Using variable to define 2d array

I'd like to paste the value of an array to a column. I've read somewhere that if I use a 2D array is not necessary to use .Transpose. I've defined a variable LRow witch contains the total number of rows in the column, using this code

Windows("export").Activate
With Sheets("export")
    LRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With

But than it seems not possible to use it to define the array like this

Dim copyArray(1 To LRow, 1 To 1)
copyArray = Worksheets("export").Range("A1:A" & LRow).Value
Windows("import").Activate
Worksheets("Foglio1").Range("A2:A" & LRow + 1) = copyArray
End Sub

I think it's because with this code I'm defining a static array.

I there any solution to this?

Upvotes: 1

Views: 28

Answers (1)

user4039065
user4039065

Reputation:

Try it as,

dim copyArray as variant
with worksheets("export")
    copyArray = .range(.cells(1, "A"), .cells(.rows.count, "A").end(xlup)).value
end with
Worksheets("Foglio1").Range("A2").resize(ubound(copyArray, 1), ubound(copyArray, 2)) = copyArray

That always works for me and is more dynamic as the target is always reshaped to match the source.

Upvotes: 1

Related Questions