Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Selecting a range, defining it to use later in a procedure.

Posted on 2014-10-29
3
Medium Priority
?
65 Views
Last Modified: 2014-10-29
I am copying 500 rows of data.  How would I recall these to use later in a procedure?
Could I use: RangeToUseLater = ??

Range(ActiveCell, ActiveCell.Offset(499, 0)).Copy

I need to run a few other thing before I get the range.  Or do I need to re-org the sequence so I get it when I need it?
0
Comment
Question by:RWayneH
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 19

Expert Comment

by:Montoya
ID: 40410983
you can simply name the range, and use it later in a formula, etc...
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 40411119
Dimension a range variable then assign this range to it like so:
Option Explicit
Dim rng as Range

Sub MyProcedure()
.
.
.
Set rng = Range(ActiveCell, ActiveCell.Offset(499, 0))
.
'do other stuff
.
.
rng.Copy
.
'
.
End Sub

Open in new window


If you're range is on a different sheet than the destination, you'll want to define the range as part of that specific worksheet.

Regards,
-Glenn
0
 

Author Closing Comment

by:RWayneH
ID: 40412375
Thanks for the help
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question