When modifying worksheets and ranges,  one option would be to modify the worksheet directly using the commands I’v explained in the article Referencing Ranges In Excel Using VBA but that would be very inefficient. Every time you read or write something to an excel sheet, you will be consuming considerable resources. A great way to improve your codes efficiency would be to minimize reading and writing to and from the excel sheet by following these steps:

1. Read all the data you need from the worksheet to an array (using one call).
2. Make all modifications required to that array.
3. Print the data from the array to the worksheet (using one call).

In this article I will explain how this can be done.

Jump To:

• Copying Data from a Range to Array
• Common Errors When Copying Data from a Range to an Array
• Processing the Array
• Copying Array Values to a Worksheet Range

Copying Data from a Range to Array:

In this example I will use the sheet below:

The code below copies the the data from A2:C11 to an array. For more information about referencing ranges please see Referencing Ranges In Excel Using VBA:

Sub Example1()
'dynamic array
Dim arrValues() As Variant
arrValues = Range(Cells(2, 1), Cells(11, 3))
End Sub


Keep in mind that whenever you copy data from a range to an array (even if the range is a single column) , you will end up with a 2D array. The arrays index will start at “1”.

Common Errors When Copying Data from a Range to an Array:

The array must be declared dynamic. Using a static array will trigger an error. The code below will trigger a Compile Error Can’t Assign to Array Error:

Sub Example2()
'static array
Dim arrValues(1 To 10) As Variant
arrValues = Range(Cells(2, 1), Cells(11, 3))
End Sub 

Another thing to keep in mind is that the array’s data type must be declared variant. The code below will trigger a Run-time error ’13’: Type mismatch Error:

Sub Example3()
'string type array
Dim arrValues() As String
arrValues = Range(Cells(2, 1), Cells(11, 3))
End Sub


Processing the Array:

After copying the required data from the range to the array, you can make the required changes to the array. The code below changes the array of variant data type to an array of strings. Although keep in mind that this conversion is not always necessary.

Sub example4()
Dim arrValues() As Variant
'the string array
Dim arrStrings() As String
Dim i As Integer
Dim j As Integer

arrValues = Range(Cells(2, 1), Cells(11, 3))
'resising the string array to match the array of values
ReDim arrStrings(1 To UBound(arrValues), 1 To UBound(arrValues, 2))
For i = 1 To UBound(arrValues)
    For j = 1 To UBound(arrValues, 2)
        arrStrings(i, j) = arrValues(i, j)
    Next j
Next i

End Sub


The following code transposes the array of data

Sub example5()
Dim arrValues() As Variant
'the transposed array
Dim arrTransposed() As String
Dim i As Integer
Dim j As Integer

arrValues = Range(Cells(2, 1), Cells(11, 3))
'resising the transposed array to match the array of values
ReDim arrTransposed(1 To UBound(arrValues, 2), 1 To UBound(arrValues))
For i = 1 To UBound(arrValues)
    For j = 1 To UBound(arrValues, 2)
        arrTransposed(j, i) = arrValues(i, j)
    Next j
Next i

End Sub


Copying Array Values to a Worksheet Range:

The code below creates a 10X3 array with the same data as the example above. It then copies the array to the range “A2:C11”. For more information about referencing ranges and working with strings please see Referencing Ranges In Excel Using VBA and VBA Excel String Processing and Manipulation

Sub example6()
Dim arrValues(1 To 10, 1 To 3) As Variant
Dim i As Integer
For i = 1 To 10
    arrValues(i, 1) = "Data" + Strings.Trim(Str(i)) + "_" + Strings.Trim(Str(1))
    arrValues(i, 2) = "Data" + Strings.Trim(Str(i)) + "_" + Strings.Trim(Str(2))
    arrValues(i, 3) = "Data" + Strings.Trim(Str(i)) + "_" + Strings.Trim(Str(3))
Next i

Range("A2:C11") = arrValues
End Sub


The result:

Note that when copying an array to a range, the array data type does not need to be a variant. We could have used the following code as well:

Sub example7()
'string data type
Dim arrValues(1 To 10, 1 To 3) As String
Dim i As Integer
For i = 1 To 10
    arrValues(i, 1) = "Data" + Strings.Trim(Str(i)) + "_" + Strings.Trim(Str(1))
    arrValues(i, 2) = "Data" + Strings.Trim(Str(i)) + "_" + Strings.Trim(Str(2))
    arrValues(i, 3) = "Data" + Strings.Trim(Str(i)) + "_" + Strings.Trim(Str(3))
Next i


Range("A2:C11") = arrValues
End Sub



Also had we chosen “A2:C5” instead of a range matching the array size, the extra rows would have been omitted:

Sub example8()
'string data type
Dim arrValues(1 To 10, 1 To 3) As String
Dim i As Integer
For i = 1 To 10
    arrValues(i, 1) = "Data" + Strings.Trim(Str(i)) + "_" + Strings.Trim(Str(1))
    arrValues(i, 2) = "Data" + Strings.Trim(Str(i)) + "_" + Strings.Trim(Str(2))
    arrValues(i, 3) = "Data" + Strings.Trim(Str(i)) + "_" + Strings.Trim(Str(3))
Next i


Range("A2:C5") = arrValues
End Sub


The result:

Had we chosen a range larger than the array dimensions, the extra rows would have been copied as “NA”:

Sub example9()
'string data type
Dim arrValues(1 To 10, 1 To 3) As String
Dim i As Integer
For i = 1 To 10
    arrValues(i, 1) = "Data" + Strings.Trim(Str(i)) + "_" + Strings.Trim(Str(1))
    arrValues(i, 2) = "Data" + Strings.Trim(Str(i)) + "_" + Strings.Trim(Str(2))
    arrValues(i, 3) = "Data" + Strings.Trim(Str(i)) + "_" + Strings.Trim(Str(3))
Next i
Range("A2:C13") = arrValues
End Sub

See Also:

• Referencing Ranges In Excel Using VBA
• VBA Excel String Processing and Manipulation
• Selecting Ranges of Variable Size

If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me. Also please visit my website www.software-solutions-online.com