Using variable to define 2d array

Question

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?

user4039065 · Accepted Answer · 2018-04-21 09:25:01Z

1

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.

answered Apr 21, 2018 at 9:25

user4039065

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

Collectives™ on Stack Overflow

Using variable to define 2d array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related