Referencing "Refers To" in a msgbox

[BrianExcel]

I set ranges using a button. All ranges are supposed to set for rows 1 - rows 191.

I'd like to program so that I can see the "refers to" section of a range in a message box. Does anyone know how I can do this?

If you are not familiar with the "refers to" property you can hit CTRL + F3 and it will give you the named ranges for your workbook. Within that, the "Refers to" column shows you which cells are contained within the range.

If I had named one of my ranges "rTestRange" how could I pull that info into a msgbox?

I tried msgbox rTestRange.refersto and rTestRange.value but nothing is working.

Thoughts?

 

[Tom Urtis]

Maybe

MsgBox rtestRange.address


????

 

[shg]

Or, guessing that you mean a named range,

Sub ShowRefersTo(sRng As String)
    Dim r As Range
    
    On Error Resume Next
    Set r = ActiveWorkbook.Names(sRng).RefersToRange
    Set r = ActiveSheet.Names(sRng).RefersToRange
    
    MsgBox """" & sRng & """ Refers to: " & r.Address(External:=True)
End Sub

 

[BrianExcel]

Thanks Tom, but this gives me an error message of:

Run Time Error: 91

Object variable or with block variable not set.

This code is actually located inside the module where the ranges are set (after they are all set of course). The msgbox is the last line of code in the sub.

 

[Tom Urtis]

Thanks Tom, but this gives me an error message of:

Run Time Error: 91

Object variable or with block variable not set.

This code is actually located inside the module where the ranges are set (after they are all set of course). The msgbox is the last line of code in the sub.

Specifically this was what I was referring to in practice, which works for me:

Sub test()
With Range("rTestRange")
MsgBox .Parent.Name & .Address
End With
End Sub

 

[BrianExcel]

Let me post a little more detail before running home...

I am setting about 10 ranges, each one the same length, all on the same sheet...

Range 1: A2:A100 (named rRangeTestA)
Range 2: B2:B100 (named rRangeTestB)
Range 3: C2:C100 (named rRangeTestC)
Range 4: D2:D100 (named rRangeTestD)
Ranges 5 - 10 etc.

Now, to make sure there are no blank spaces in my data, I want to populate a message box with the values above so I can see where they have been set.

In other words, the message box would show:

rRangeTestA set to: A2:A100
rRangeTestB set to: B2:B100
rRangeTestC set to: C2:C100
rRangeTestD set to: D2:D100

And so on...does this make more sense? How would I get the range data to display in a msgbox?