• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy selected range to array (same size) [SOLVED]

A

arishy

Member
There must be a way to fill an array as easy as copy a range to another (copy and paste).

Let us say I have a named range A1:D5. And I have an array dimentioned with the same size

Array(4,3).


What I want to do copy the range to the array, without having to double loop...etc


Is that doable ??
 
Hi, arishy!

Tried this?

-----

Dim vArray() as Variant

vArray = Range("A1:C4")

-----

Regards!
 
Two birds with one stone.!! Not only it IS better than copy and paste but dynamic too.

I do not need to worry about the array size.It will size it according to the range properties..... WoW.


If I repeat the operation (looping through a worksheet) with a different range(s), do I have to redim the array ?
 
Hi, arishy!

No, you don't have to. Full array assignation implies a ReDim without Preserve. Play with this code:

-----

[pre]
Code: 
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim vArray() As Variant
vArray = Selection
Debug.Print Selection.Address, UBound(vArray, 1), UBound(vArray, 2), vArray(UBound(vArray, 1), UBound(vArray, 2))
End Sub
[/pre]
-----

Regards!
 
Hi, arishy!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!
 
Arishy: This is also worth a read:

Data Transfer between worksheet and VBA

http://www.cpearson.com/excel/ArraysAndRanges.aspx
 
Excellent work in the subject of Arrays; which is a big topic. Thanks for sharing.

In the link, I noticed this statement that did not work for me.


Set Destination = Range("K1").Resize(NumRows, NumCols).Value = Arr ( The last paragraph)


It caused errors. Changing the first = to Dot did not work either.

Can you help ( I know my original problem is marked solved ) So, I hope I am not breaking any rules here
 
Hi Arishy


It is OK it was your thread after all.


I think what Chip wanted to do was make the Destination equal to the variable Range then he wanted to push the Variant into that newly resized Range. The give away was in the Set Statement. He is setting a range while also trying to push a value into that range. Set your range first then push your data into that range. I think the following is more in line with what is required;

[pre]
Code: 
Set destination = Range("K1").Resize(NumRows, NumCols)
destination = Arr
[/pre]

Give this a crack and let us know how you go.


Take care


Smallman
 
You must log in or register to reply here.
Back
Top