Excel Name.RefersToRange: how to check if Name object refers to a range?

Question

The Microsoft documentation says about RefersToRange:

If the Name object doesn't refer to a range (for example, if it refers to a constant or a formula), this property fails.

I'm trying to iterate over all the named ranges in a worksheet.

Private Sub Something()

    Dim namedRanges As names
    Set namedRanges = ActiveSheet.names

    Dim targetSheet As Worksheet
    Set targetSheet = Sheet1
    targetSheet.Cells.Clear

    Dim i As Integer
    For i = 1 To namedRanges.count
        targetSheet.Cells(i, 2).Value = namedRanges(i).Name
        targetSheet.Cells(i, 3).Value = namedRanges(i).RefersToRange.Address
    Next


End Sub

In the above my code will fail if any NamedRange doesn't refer to a range. How can I check if the Name object refers to a range so my code doesn't fail?

UPDATE

I found a hack to do this but I'd prefer a cleaner way. I check if the string value of the named range contains a dollar sign which indirectly tells me if it contains an address value (which a range would):

For i = 1 To namedRanges.count
    targetSheet.Cells(i, 2).Value = namedRanges(i).Name
    If InStr(namedRanges(i).Value, "$") > 0 Then
        targetSheet.Cells(i, 3).Value = namedRanges(i).RefersToRange.Address
    End If
Next

Answer 1

EDIT: I've changed the error handling to omit constants Not that I advocate this approach for most problems but you could use

on error goto "label"

which would look something like

Private Sub Something()

Dim namedRanges As names
Set namedRanges = ActiveSheet.names

Dim targetSheet As Worksheet
Set targetSheet = Sheet1
targetSheet.Cells.Clear

Dim i As Integer

'skip the errors
on error goto skipName
'set start of data range
Row = 2
For i = 1 To namedRanges.count

    targetSheet.Cells(Row, 3).Value = namedRanges(i).RefersToRange.Address
    targetSheet.Cells(Row, 2).Value = namedRanges(i).Name
Row = Row + 1
skipName:
Next

'reinstate normal error trapping
on error goto 0

End Sub

The error handling will now skip listing the names when Referstorange fails.

Answer 2

Something like this which tests if there is a valid intersect in the usedrange of the sheet of interest with your range name

Caveat: This does assume that your usedrange does cover your potential range names. Which I think is a safe assumption

Also you I don't think you can work with ActiveSheet.Names in the way you tried.

Private Sub Something()
    Dim nmRng As Name
    Dim rng1 As Range
    Dim rng2 As Range
    Set rng1 = Sheets(1).UsedRange
    On Error Resume Next
    For Each nmRng In ActiveWorkbook.Names
        Set rng2 = Nothing
        Set rng2 = Intersect(rng1, Range(nmRng))
        If Not rng2 Is Nothing Then Debug.Print nmRng & " " & nmRng.RefersTo.Address
    Next
    On Error GoTo 0
End Sub