define an array using 2 separate columns

Question

I have a table of data with many columns. I want to create an array with just the data from 2 of the columns. So far I've just been defining arrays like this.

finalrow = Sheet2.Range("A1").End(xlDown).row
finalcolumn = Sheet2.Range("A1").End(xlToRight).column
FTSE100 = Sheet2.Range("A1").Resize(finalrow, finalcolumn)

And I would really like to define it as a 2 dimensional array of the form

FTSE100 = "column A to finalrow" & "column G to finalrow"

Unfortunately, my google-fu hasn't turned up how to define an array using 2 non-adjacent data sets. Help or advice would be much appreciated.

you wan't 2 columns in a 1-dimensional array?? or do you mean a 2-dimensional array? — Goos van den Bekerom
– Goos van den Bekerom, Commented Oct 29, 2014 at 14:06
Sorry, should have clarified (since edited), 2-dimensional array — Kaz
– Kaz, Commented Oct 29, 2014 at 14:14
I'm researching this a little bit, and its not easy. But I can't think of any reason why you would want an array like this. — Goos van den Bekerom
– Goos van den Bekerom, Commented Oct 29, 2014 at 14:23

Community · Accepted Answer · 2017-05-23 12:27:22Z

3

Define the two ranges independently, then use UNION to combine them. Such as:

Set myRange = Union(columnARange, columnGRange)

More info available here: How to create a range from 2 ranges in VBA

edited May 23, 2017 at 12:27

CommunityBot

11 silver badge

answered Oct 29, 2014 at 14:28

ExactaBox

3,4251 gold badge22 silver badges31 bronze badges

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

4 Comments

Kaz Over a year ago

it doesn't seem to be working properly, it's throwing a "method or data member not found" when I try to use "myrange" to define the array

ExactaBox Over a year ago

you probably have to initialize ("Dim") myRange before using it.

ExactaBox Over a year ago

You specifically say you don't want a contiguous range -- your question asks about "non-adjacent" sets

Kaz Over a year ago

Apologies, I've gone back and written it from scratch again and for some reason it's working this time. I don't know what I missed last time. Thanks for your help.

Collectives™ on Stack Overflow

define an array using 2 separate columns

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related