Importing range into 2d array?

GLIBBY

New Member
Joined
Jun 14, 2010
Messages
37
Hello all. Is there a way to import a range into a 2d array, without having to use a loop. Ive read that you can create an array from a range using
Code:
array=range("A1:A10").resize(1,10)
but Im not sure how to do that for just one row of a 2d array.

I have a 2D array with 2 rows and 10 columns. I want the first row (array(1,1) to array (1,10)) to be data from a workbook range. Then the second row will be data resulting from calculations done to row 1. I've tried using the above code to load the range into the array, but it only changes the value of array(1,10).
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Do you want to do the calculations in VBA *after* the first part has been loaded?

You could potentially load the whole range, then loop through columns 2 to 7 (in this example) and set those values to 0. Something like:

Code:
Sub LoadArray()
    Dim arData()
    Dim Rng As Range
    Dim i As Integer
    Dim j As Integer
 
    Set Rng = Range("A1").CurrentRegion
    arData = Rng.Value
 
    For i = LBound(arData) To UBound(arData)
        For j = 2 To 7
            arData(i, j) = 0
        Next j
    Next i
 
    Range("A50").Resize(UBound(arData), 7).Value = arData
End Sub

Note:
When you load an array from a worksheet it's 1-based, not 0-based.

The above code loads the data in the table containing A1, places 0 values in all but the first column, and then reads the data back to the worksheet as a check.

Denis
 
Upvote 0
I dont quite follow what you're saying.

Yes, I do want to first load the data in the array then perform calculations. I suppose I could make two separate arrays, I was planning on keeping it as the same one for convenience.

Basically I wanted to have a 2x10 array. The first "row" of the array (locations 1,1 through 1,10) would be values from a range, say A1:A10. I want to load that all at once, since the range will exist prior to loading the array. After the data is in the array, I want to perform calculations, say array(1,1) + 1, array(1,2)+2 and so on. These values would be stored in array(2,1) to array (2,10). Does that make sense?
 
Last edited:
Upvote 0
Hello,

Something like this perhaps?

Code:
Sub foo()
Dim varArr() As Variant
Dim i As Long, j As Long
Let varArr = Range("A1:A10").Value
Let j = UBound(varArr, 1)
ReDim Preserve varArr(1 To j, 1 To j)
For i = LBound(varArr, 1) To j
    Let varArr(i, 2) = varArr(i, 1) * 2
    Debug.Print varArr(i, 1), varArr(i, 2)
Next
End Sub
 
Last edited:
Upvote 0
There must be some problem with how Im defining my range. I tried adapting your code for my application and I get a type mismatch. My actual range is defined as:

Code:
Dim a() As Variant
        
    
    Worksheets("Sheet1").Select
    Let a = Range("C1263, I1263, O1263, U1263, AA1263, AG1263, AM1263, AS1263, AY1263, BE1263").Value

Not quite sure what Im doing wrong here?
 
Upvote 0
If your range is not contiguous I don't think you can use a variant to create a 2-d array directly. For example:

Dim vArr as Variant
vArr = Range("A1,C1,E1,G1").Value
will not work, but
vArr = Range("A1:G1")
will work
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top