Add two Ranges to Array

[JStellato]

Hello, I need to add two ranges to an array. My current code only has one range, I've tried to add a second range, but my attempts have been futile.

Here's my current code:

MyArray = Range([a3], [h30])

I've tried

MyArray = Range([a3], [h30]) & Range([i3], [p30])

and

MyArray = Range([a3], [h30])
MyArray = MyArray + Range([i3], [p30])

none of these are the right answer. Anyone have any ideas?

Thanks!

 

[brettdj]

If you want to put two ranges in one array then something like this

Dim MyArray
MyArray = Range("A3:h30, j3:p30")

If you wanted to add say the values of two cells together withing the array (ie cell 1 from the first range area, to cell 1 of the second A3+J3) then you should
- set up two arrays (one for each range)
- loop through the array and add the second array values to the first

Cheers

Dave

 

[JStellato]

I gave this a shot, but haven't been able to make it work. I'm using a variable as my column, but I didn't think that would matter this code:

varLastRow = "p30"
MyArray = Range("a3:varLastRow, i3:varLastRow")

Also tried

MyArray = Range("a3:[varLastRow], i3:[varLastRow]")

With both I get "Method Range of Object _Global failed" Runtime error 1004

 

[DJAXE]

Putting aside the suggestion that you loop through your arrays and add them together, your proposed code has a syntax error.

Try:

varLastRow = "p30" MyArray = Range("a3:" & varLastRow &", i3:" &varLastRow)</pre>

However, I'm not sure that produces the correct result.

Based on your previous posts, perhaps use:

varLastRow = 30 MyArray = Range("a3:h" & varLastRow &", i3:p" &varLastRow)</pre>

 

[JStellato]

Thanks, this actually worked, but I ran into another issue. I think Brettdj might have seen the writing on the wall when he said:

- set up two arrays (one for each range)
- loop through the array and add the second array values to the first

With the code

MyArray = Range("A3:h30, j3:p30")

This code creates an array that's 16 columns wide and 30 rows high, but I really want an array that's 8 columns wide and 60 rows. However I'm so new to working with array's I have no idea how to setup the two and add them together.

 

[chuckchuckit]

JStellato - Working with arrays can be a bit tricky as they don't always allow things one would think one could do. Such as you can not directly copy one array to an other array (even if they are exactly the same size). You have to loop through each element and copy one at a time. Not sure if same applies to appending etc?

But they sure are a lot faster than using cells to do things in. I use arrays sometimes for processing cell data instead of the worksheet cells. Then copy answers to cells etc.

Here is a good link that has a ton of array functions, and some discussion on what each one does.

http://www.cpearson.com/excel/vbaarrays.htm

Hope it is of assistance.

Chuck

 

[brettdj]

This code creates an array that's 16 columns wide and 30 rows high, but I really want an array that's 8 columns wide and 60 rows. However I'm so new to working with array's I have no idea how to setup the two and add them together.

You have a couple of options
1) Looping through two smaller arrays and combining into a larger block (quite straightforward, I can provide code for this if useful)
2) Move the data in your original sheet into a 8*60 rather than 16*30
3) If you need to maintain the original sheet position, duplicate the sheet, then move the range, do your code

I'd go with (1). Looping through arrays (not ranges) is very very quick

Cheers

Dave

 

[JStellato]

Hey thanks for all the help guys. I think I'd like the code for your option 1 Brettdj,

1) Looping through two smaller arrays and combining into a larger block (quite straightforward, I can provide code for this if useful)

I think this is what I need to do.

Thanks!

 

[JStellato]

BTW, I did try this link...

http://www.cpearson.com/excel/vbaarrays.htm

Which was helpful in understanding what's going on, I checked the combine2darray function and attempted to implement it, but I wasn't able to get it working. The example though was exactly what I was looking for.

I'd still like to see your code Brettdj for looping through them and combining

Thanks!

 

[brettdj]

Something like this

The first array (Y) is built to the desired end length (60*8), with the first half of the array holding the existing values in A1:H30 (ie 30*8)

Then a second array (X) is created looking at I1:P30

This array is looped through and the results appended to the second half of the first array

The code is dynamic when the two arrays combined are the same size, ie

  X(UBound(Y, 1) + lngRow, lngCol) = Y(lngRow, lngCol)

for the first elemement in array 1 will write to
X(30+1,1) = Y(1,1)
X(31,1) = Y(1,1)

down to
X(30+30,8) = Y(30,8)
X(60,8) = Y(30,8)

So you end up with a 60*8 Y array

hth

Dave

Sub BuildArray()
    Dim X
    Dim Y
    Dim lngRow As Long
    Dim lngCol As Long
    X = Range([A1], [H60])
    Y = Range([I1], [p30])
    For lngRow = 1 To UBound(Y, 1)
        For lngCol = 1 To UBound(Y, 2)
            X(UBound(Y, 1) + lngRow, lngCol) = Y(lngRow, lngCol)
        Next
    Next
End Sub