Save range to variable

Question

I wrote some functional VBA:

Sheets("Src").Range("A2:A9").Copy Destination:=Sheets("Dest").Range("A2")

I want to extract the source range into a variable for flexibility.

SrcRange = Sheets("Src").Range("A2:A9")
SrcRange.Copy Destination:=Sheets("Dest").Range("A2")

However, this doesn't work. What SrcRange should be Dimmed as? Is the first line even correct?
I tried Dimming SrcRange as Range and it gave me
Runtime error 91: Object Variable or With block variable not set

I'm not very familiar with the language and the documentation has left me wanting (I couldn't find the return type to the Sheets(index) invocation, this was the closest I found). When I hit Record Macro, perform some actions, and hit stop, the Macro body is still blank.

Could anyone shed some light on how to use SrcRange as a variable?

Answer 1

... 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.

Answer 2

To save a range and then call it later, you were just missing the "Set"

Set Remember_Range = Selection    or    Range("A3")
Remember_Range.Activate

But for copying and pasting, this quicker. Cuts out the middle man and its one line

Sheets("Copy").Range("A3").Value = Sheets("Paste").Range("A3").Value

Answer 3

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.

Answer 4

My use case was to save range to variable and then select it later on

Dim targetRange As Range
Set targetRange = Sheets("Sheet").Range("Name")
Application.Goto targetRange
Set targetRangeQ = Nothing ' reset

Answer 5

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").Value = Src 'Write array back to another range