How to determine the height of a Range as an Integer

[TechWrangler]

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

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

[somethinglikeant]

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

[TechWrangler]

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

[Dick Kusleika]

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


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

[JMB]

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

[TechWrangler]

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 **** K. for the code snippets I will use both of them.