Loading ranges into array

[sijpie]

If I declare a 2 dimensional array I can load a range into it fairly simple:

dim MyArr(1 to 10,1 to 4)
 
MyArr = Range("A1:D10")

But what if I want to add say A1:A10, C1:C10, F1:F10 and G1:G10 into the array. I have tried:

MyArr(1) = Range("A1:A10")

but vba doesn't seem to like that.

Any smart way to do this (I try to avoid looping)?

Can you address just one column of the array? (or one row for that matter)

 

[shg]

One way, using a method I learned from pgc01:

    Dim av As Variant
 
    With Application
        av = .Index(Cells, [row(1:10)], Array(1, 3, 6, 7))
    End With

BTW, you're not accomplishing anything by dimensioning the array here:

dim MyArr[COLOR=red](1 to 10,1 to 4)[/COLOR]
 
MyArr = Range("A1:D10")

 

[sijpie]

thanks shg, I'll experiment with your method.

re the dimensioning, no that was just for the example, would never do that in real life.

 

[sijpie]

works a treat!

Now experimenting with reading out a single row I got this:

Sub test1()
 
 Dim av() As Variant
 Dim i As Long
 
    With Application
        av = .Index(Cells, [row(1:10)], Array(1, 3, 6, 2))
    End With
    
'    Now right out one row from the array into a range:
    i = LBound(av) + 1     'read out 2nd row
    Range("A20:D20") = Array(av(i, 1), av(i, 2), av(i, 3), av(i, 4))
    
End Sub

This method of loading columns into an array is also great for moving columns about.

In my case I need it to read in a large range, and then write individual lines out to several sheets where the column order of the data is different from the entry sheet.

Thanks again shg

 

[shg]

Sub test1()
    Dim av        As Variant    ' the bowlegs don't do anything useful
 
    With Application
        av = .Index(Cells, [row(1:10)], Array(1, 3, 6, 2))
        ' write out second row:
        Range("A20:D20").Value = .Index(av, 2, 0)
    End With
End Sub

 

[sijpie]

even less typing!

great. I'll need to look into this index property a bit more.

 

[shg]

You're welcome, glad it worked for you.

 

[sijpie]

shg, I do have another question about your notation:

I can't seem to make the rows to be read dynamic - if I replace one of the numbers with a variable, the compiler fails:

av = .Index(Cells, [row(1:myRow)], Array(1, 3, 6, 2))

Now the [] are a short for the function Evaluate, as I understand, so I thought I may be able to do this using Evaluate, but I can't get any further there either. Evaluate(row(1:10)) also fails.

Suggestions?

 

[sijpie]

Ah, forget it, I've found it -

        av = .Index(Cells, Evaluate("row(4:" & myRow & ")"), Array(1, 3, 6, 2))

does the trick.

God knows how it works though...