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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

In your own answer  you effectively do this  Dim SrcRange As Range   you should always declare things explicitly Set SrcRange   Sheets  quot Src quot   Range  quot A2 A9 quot   SrcRange Copy Destination  Sheets  quot Dest quot   Range  quot A2 quot    You re not really  quot extracting quot  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  quot Src quot   Range  quot A2 A9 quot   Value  Read range to array  Here you can add code to manipulate your Src array      Sheets  quot Dest quot   Range  quot A2 A9 quot   Value   Src  Write array back to another range

User · Answer

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

[excel] Save range to variable

Examples related to excel

Examples related to vba