Consider this four dimensional array

Dim SampleArray(0 To 1, 0 To 3, 0 To 2, 0 To 5) As Variant |

It's structure can be best explained as follows.

So now let's look at how we can declare and populate multidimensional array. I will use below sample data for the explanations.

So let's put all these data to an array called All_Data()

Dim WS As Worksheet Dim All_Data(0 To 17, 0 To 4) As Variant Dim i, j As Long Set WS = Worksheets("Sheet1") For i = 0 To 17 For j = 0 To 4 All_Data(i, j) = WS.Range("A2").Offset(i, j).Value Next j Next i |

It is a best practice to use LBound and UBound instead of constants. Because then we don't need to change whole code if we need to change the size of our array. So here is the modification of above code where constants are replaced with LBound and UBound functions.

Dim WS As Worksheet Dim All_Data(0 To 17, 0 To 4) As Variant Dim i, j As Long Set WS = Worksheets("Sheet1") For i = LBound(All_Data, 1) To UBound(All_Data, 1) For j = LBound(All_Data, 2) To UBound(All_Data, 2) All_Data(i, j) = WS.Range("A2").Offset(i, j).Value Next j Next i |

So now you know how to put values from worksheet to a multidimensional array. So what if you need to do the reversal. ie-What if you need to put values from an array to a worksheet range. Code is very much similar to the above example. You just need to do a change in one line. Following is an example. Here what we do is, first we get data from first 3 columns to an array. Then reads values from array and put them back to sheet 3.

Dim WS As Worksheet Dim All_Data(0 To 17, 0 To 2) As Variant Dim i, j As Long Set WS = Worksheets("Sheet1") For i = LBound(All_Data, 1) To UBound(All_Data, 1) For j = LBound(All_Data, 2) To UBound(All_Data, 2) All_Data(i, j) = WS.Range("A2").Offset(i, j).Value Next j Next i Dim WS_New As Worksheet Set WS_New = Worksheets("Sheet3") For i = LBound(All_Data, 1) To UBound(All_Data, 1) For j = LBound(All_Data, 2) To UBound(All_Data, 2) WS_New.Range("A1").Offset(i, j).Value = All_Data(i, j) Next j Next i |

Here is the result you will get.