debug.print range "full" address

[Patti]

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

 

[Bob Phillips]

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

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

 

[Tom Ogilvy]

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

 

[Doug Glancy]

Patti,

Use the "External" argument to Address:

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

hth,

Doug

 

[Patti]

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]

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.