This is concerning my other post, but a much simpler version.

How do you determine the address of a Range in VBA?


--
TechWrangler


------------------------------------------------------------------------
TechWrangler's Profile: http://www.excelforum.com/member.php...o&userid=34244
View this thread: http://www.excelforum.com/showthread...hreadid=540004

use expression.Address

this code prints the address of the current selection to the Immediate
window
Sub PrintAddress()
Debug.Print Selection.Address
End Sub

somethinglikeant

Thanks for the quick response. I want to use this as my loop control
variable so I can loop through a varible size range and copy the
elements to another Worksheet. So it doesn't need to print. Would I
still use this?

Or would I do something like:

set r = Range ("CRP01")
Dim i = r.????
Dim crpCount = 2

For crpRow = crpCount To i
Code...
Next crpRow


--
TechWrangler


------------------------------------------------------------------------
TechWrangler's Profile: http://www.excelforum.com/member.php...o&userid=34244
View this thread: http://www.excelforum.com/showthread...hreadid=540004

r.Rows.Count will return the number of rows in the range.


--
Dick Kusleika
MS MVP - Excel
www.dailydoseofexcel.com

TechWrangler wrote:
Thanks for the quick response. I want to use this as my loop control
variable so I can loop through a varible size range and copy the
elements to another Worksheet. So it doesn't need to print. Would I
still use this?

Or would I do something like:

set r = Range ("CRP01")
Dim i = r.????
Dim crpCount = 2

For crpRow = crpCount To i
Code...
Next crpRow

Is there a specific reason to loop through each cell in the range to copy it
to the other sheet? If you know the beginning and ending cells of your
source range, and the upper left cell of your destination range you can just
copy the cells right over.


Dim rngCopy As Range
Dim rngDest As Range

Set rngCopy = Worksheets("Sheet2").Range(Range("Cell1"), _
Range("Cell2"))
Set rngDest = Worksheets("Sheet3").Range("A1")

rngCopy.Copy rngDest



"TechWrangler" wrote:


Thanks for the quick response. I want to use this as my loop control
variable so I can loop through a varible size range and copy the
elements to another Worksheet. So it doesn't need to print. Would I
still use this?

Or would I do something like:

set r = Range ("CRP01")
Dim i = r.????
Dim crpCount = 2

For crpRow = crpCount To i
Code...
Next crpRow


--
TechWrangler


------------------------------------------------------------------------
TechWrangler's Profile: http://www.excelforum.com/member.php...o&userid=34244
View this thread: http://www.excelforum.com/showthread...hreadid=540004

I am creating a template with the data that is copied into sheet1. The
user gets to choose which type of template they want and then it is
built using this macro. The ranges in both of the other sheets are
dynamic so I never know how big they are.

I would like to thank both JMB and Dick K. for the code snippets I will
use both of them.


--
TechWrangler


------------------------------------------------------------------------
TechWrangler's Profile: http://www.excelforum.com/member.php...o&userid=34244
View this thread: http://www.excelforum.com/showthread...hreadid=540004