Check if a named range refers to a constant or a range

[billj]

Hi everyone,

I have a macro below to modify named range to right sizes. But the macro will fail if there are some names which refer to constant or formula, instead of a range. I think nm.RefersToRange.Name will fail if nm does not refer to a range.

My question is: is there a way to test if a name refers to a constant? If so, please don't do anything about that name. For other names that refer to a range, then action is taken to process them.

Please Login or Register  to view this content.

End Sub

[Norie]

Don't know if it's fool proof but how about testing for '!' in RefersTo?

Please Login or Register  to view this content.

[JEC.]

Try this!

Please Login or Register  to view this content.

[billj]

Thanks to Norie and JEC. I am happy to close the thread now - your solutions work nicely!