Save range to variable
In your own answer, you effectively do this:
Dim SrcRange As Range ' you should always declare things explicitly
Set SrcRange = Sheets("Src").Range("A2:A9")
SrcRange.Copy Destination:=Sheets("Dest").Range("A2")
You're not really "extracting" the range to a variable, you're setting a reference to the range.
In many situations, this can be more efficient as well as more flexible:
Dim Src As Variant
Src= Sheets("Src").Range("A2:A9").Value 'Read range to array
'Here you can add code to manipulate your Src array
'...
Sheets("Dest").Range("A2:A9").Value = Src 'Write array back to another range
Just to clarify, there is a big difference between these two actions, as suggested by Jean-François Corbett.
One action is to copy / load the actual data FROM the Range("A2:A9")
INTO a Variant Array called vArray
(Changed to avoid confusion between Variant Array and Sheet both called Src):
vArray = Sheets("Src").Range("A2:A9").Value
while the other simply sets up a Range variable (SrcRange) with the ADDRESS of the range Sheets("Src").Range("A2:A9")
:
Set SrcRange = Sheets("Src").Range("A2:A9")
In this case, the data is not copied, and remains where it is, but can now be accessed in much the same way as an Array. That is often perfectly adequate, but if you need to repeatedly access, test or calculate with that data, loading it into an Array first will be MUCH faster.
For example, say you want to check a "database" (large sheet) against a list of known Suburbs and Postcodes. Both sets of data are in separate sheets, but if you want it to run fast, load the suburbs and postcodes into an Array (lives in memory), then run through each line of the main database, testing against the array data. This will be much faster than if you access both from their original sheets.
... And the answer is:
Set SrcRange = Sheets("Src").Range("A2:A9")
SrcRange.Copy Destination:=Sheets("Dest").Range("A2")
The Set
makes all the difference. Then it works like a charm.