Microsoft Office Tutorials and References
In Depth Information
Working with Ranges
In this example, the first message box displays 600 (the number of rows in the original range),
and the second message box displays 12 (the number of columns). You’ll find that transferring
the range data to a variant array is virtually instantaneous.
The following example reads a range (named data ) into a variant array, performs a simple
multiplication operation on each element in the array, and then transfers the variant array back to the
Dim x As Variant
Dim r As Long, c As Integer
‘ Read the data into the variant
x = Range(“data”).Value
‘ Loop through the variant array
For r = 1 To UBound(x, 1)
For c = 1 To UBound(x, 2)
‘ Multiply by 2
x(r, c) = x(r, c) * 2
‘ Transfer the variant back to the sheet
Range(“data”) = x
You’ll find that this procedure runs amazingly fast. Working with 30,000 cells took less than one
A workbook that contains this example is available on the companion CD-ROM. The file
is named variant transfer.xlsm .
Selecting cells by value
The example in this section demonstrates how to select cells based on their value. Oddly, Excel
doesn’t provide a direct way to perform this operation. My SelectByValue procedure follows.
In this example, the code selects cells that contain a negative value, but you can easily change
the code to select cells based on other criteria.
Dim Cell As Object
Dim FoundCells As Range
Dim WorkRange As Range
If TypeName(Selection) <> “Range” Then Exit Sub
‘ Check all or selection?