debug.print range "full" address
 

I'm trying to wrap my head around the idea of using the dot (which I seen
referred to as a "full stop") when setting a range that is not on the active
sheet. When to use it, how to use it, etc.

So I'm playing around with it, and the question is, when I do a debug.print
to test my code, is there any way to return the "full address" of the range?
Example: The following returns $C$2:$C$3 but I'd like to see something
like Worksheets("testSheet").$C$2:$C$3 so that I can be sure I've set the
range correctly.

(I'm not sure the following are the best examples, but you get the point : )

Thanks,

Patti

Sub usedRangeTest()
Dim myRange As Range
With Worksheets("testSheet")
Set myRange = .Range(.Cells(2, 3), .Cells(2, 3).End(xlDown))
Debug.Print myRange.Address ' returns $C$2:$C$3
End With
End Sub

Another example would be:

With Worksheets("Data")
lstRow = Range("g" & Rows.Count).End(xlUp).Row
Debug.Print lstRow ' *** returns 497, but I want to verify sheet name too.
End With

debug.print range "full" address
 

You won't get it from the range, but you can construct it from the range

Debug.Print "Worksheets(" & myRange &.parent.name & ")." & myRange.Address

--
HTH

Bob Phillips

"Patti" wrote in message
...
I'm trying to wrap my head around the idea of using the dot (which I seen
referred to as a "full stop") when setting a range that is not on the
active
sheet. When to use it, how to use it, etc.

So I'm playing around with it, and the question is, when I do a
debug.print
to test my code, is there any way to return the "full address" of the
range?
Example: The following returns $C$2:$C$3 but I'd like to see something
like Worksheets("testSheet").$C$2:$C$3 so that I can be sure I've set the
range correctly.

(I'm not sure the following are the best examples, but you get the point
: )

Thanks,

Patti

Sub usedRangeTest()
Dim myRange As Range
With Worksheets("testSheet")
Set myRange = .Range(.Cells(2, 3), .Cells(2, 3).End(xlDown))
Debug.Print myRange.Address ' returns $C$2:$C$3
End With
End Sub

Another example would be:

With Worksheets("Data")
lstRow = Range("g" & Rows.Count).End(xlUp).Row
Debug.Print lstRow ' *** returns 497, but I want to verify sheet name
too.
End With

debug.print range "full" address
 

Sub usedRangeTest()
Dim myRange As Range
With Worksheets("testSheet")
Set myRange = .Range(.Cells(2, 3), .Cells(2, 3).End(xlDown))
Debug.Print myRange.Address(0,0,,True)
End With
End Sub

--
Regards,
Tom Ogilvy


"Patti" wrote in message
...
I'm trying to wrap my head around the idea of using the dot (which I seen
referred to as a "full stop") when setting a range that is not on the
active
sheet. When to use it, how to use it, etc.

So I'm playing around with it, and the question is, when I do a
debug.print
to test my code, is there any way to return the "full address" of the
range?
Example: The following returns $C$2:$C$3 but I'd like to see something
like Worksheets("testSheet").$C$2:$C$3 so that I can be sure I've set the
range correctly.

(I'm not sure the following are the best examples, but you get the point
: )

Thanks,

Patti

Sub usedRangeTest()
Dim myRange As Range
With Worksheets("testSheet")
Set myRange = .Range(.Cells(2, 3), .Cells(2, 3).End(xlDown))
Debug.Print myRange.Address ' returns $C$2:$C$3
End With
End Sub

Another example would be:

With Worksheets("Data")
lstRow = Range("g" & Rows.Count).End(xlUp).Row
Debug.Print lstRow ' *** returns 497, but I want to verify sheet name
too.
End With

debug.print range "full" address
 

Patti,

Use the "External" argument to Address:

Debug.Print myRange.Address(external:=True)

hth,

Doug

"Patti" wrote in message
...
I'm trying to wrap my head around the idea of using the dot (which I seen
referred to as a "full stop") when setting a range that is not on the
active
sheet. When to use it, how to use it, etc.

So I'm playing around with it, and the question is, when I do a
debug.print
to test my code, is there any way to return the "full address" of the
range?
Example: The following returns $C$2:$C$3 but I'd like to see something
like Worksheets("testSheet").$C$2:$C$3 so that I can be sure I've set the
range correctly.

(I'm not sure the following are the best examples, but you get the point
: )

Thanks,

Patti

Sub usedRangeTest()
Dim myRange As Range
With Worksheets("testSheet")
Set myRange = .Range(.Cells(2, 3), .Cells(2, 3).End(xlDown))
Debug.Print myRange.Address ' returns $C$2:$C$3
End With
End Sub

Another example would be:

With Worksheets("Data")
lstRow = Range("g" & Rows.Count).End(xlUp).Row
Debug.Print lstRow ' *** returns 497, but I want to verify sheet name
too.
End With

debug.print range "full" address
 

Thanks Bob,Tom & Doug. It is nice to see a variety of solutions.

On this one, I had to change "Worksheets(" & myRange &.parent.name & ")." &
myRange.Address to:
"Worksheets(" &
myRange.Parent.Name & ")." & myRange.Address

Very useful though, because now I know that .Parent.Name refers to the
Workbook, and myRange.Parent.Name refers to the Worksheet.

Regards,

Patti


"Bob Phillips" wrote in message
...
You won't get it from the range, but you can construct it from the range

Debug.Print "Worksheets(" & myRange &.parent.name & ")." & myRange.Address

--
HTH

Bob Phillips

"Patti" wrote in message
...
I'm trying to wrap my head around the idea of using the dot (which I seen
referred to as a "full stop") when setting a range that is not on the
active
sheet. When to use it, how to use it, etc.

So I'm playing around with it, and the question is, when I do a
debug.print
to test my code, is there any way to return the "full address" of the
range?
Example: The following returns $C$2:$C$3 but I'd like to see something
like Worksheets("testSheet").$C$2:$C$3 so that I can be sure I've set the
range correctly.

(I'm not sure the following are the best examples, but you get the point
: )

Thanks,

Patti

Sub usedRangeTest()
Dim myRange As Range
With Worksheets("testSheet")
Set myRange = .Range(.Cells(2, 3), .Cells(2, 3).End(xlDown))
Debug.Print myRange.Address ' returns $C$2:$C$3
End With
End Sub

Another example would be:

With Worksheets("Data")
lstRow = Range("g" & Rows.Count).End(xlUp).Row
Debug.Print lstRow ' *** returns 497, but I want to verify sheet name
too.
End With

debug.print range "full" address
 

Sorry, that was my typo trying to tidy it up.

Parent is a bit more flexible than that. Parent is the container, for a
range it is the worksheet, for a worksheet it is the workbook, etc.

--
HTH

Bob Phillips

"Patti" wrote in message
...
Thanks Bob,Tom & Doug. It is nice to see a variety of solutions.

On this one, I had to change "Worksheets(" & myRange &.parent.name & ")."
&
myRange.Address to:
"Worksheets(" &
myRange.Parent.Name & ")." & myRange.Address

Very useful though, because now I know that .Parent.Name refers to the
Workbook, and myRange.Parent.Name refers to the Worksheet.

Regards,

Patti


"Bob Phillips" wrote in message
...
You won't get it from the range, but you can construct it from the range

Debug.Print "Worksheets(" & myRange &.parent.name & ")." &
myRange.Address

--
HTH

Bob Phillips

"Patti" wrote in message
...
I'm trying to wrap my head around the idea of using the dot (which I
seen
referred to as a "full stop") when setting a range that is not on the
active
sheet. When to use it, how to use it, etc.

So I'm playing around with it, and the question is, when I do a
debug.print
to test my code, is there any way to return the "full address" of the
range?
Example: The following returns $C$2:$C$3 but I'd like to see
something
like Worksheets("testSheet").$C$2:$C$3 so that I can be sure I've set
the
range correctly.

(I'm not sure the following are the best examples, but you get the
point
: )

Thanks,

Patti

Sub usedRangeTest()
Dim myRange As Range
With Worksheets("testSheet")
Set myRange = .Range(.Cells(2, 3), .Cells(2, 3).End(xlDown))
Debug.Print myRange.Address ' returns $C$2:$C$3
End With
End Sub

Another example would be:

With Worksheets("Data")
lstRow = Range("g" & Rows.Count).End(xlUp).Row
Debug.Print lstRow ' *** returns 497, but I want to verify sheet name
too.
End With