Finding a name for a range pointing to "this"?

[Maury Markowitz]

I have a query that returns rows in a deliberately random sorting that
gets pasted into a sheet called "Input". I have a number of other
sheets that refer to data in that page, using named ranges. When I
paste in the data, I use:

theSheet.Names.Add Name:=theName, RefersToR1C1:="=Input!R" & fundLoop
& "C" & ccyLoop

to give it a Name. "theName" is a properly formatted string, similar
to "

I've noticed that if the cell in question already contains a Name,
this line of code will fail. I need to find a solution for this.

Ideally I'd like to remove any existing names that point into this
sheet. However, I can't seem to find a simple way to do this. The
Names collection has all of them, not just the ones on this sheet. Is
there a simple way to isolate these?

Maury

 

[Gary Keramidas]

you can try this, it looks at the active sheet. it may work for you. (watch for
word wrap)

Sub name_ranges() 'this deletes all named ranges
Dim nm As Name

Select Case MsgBox("Are you Sure You Want To Delete All Named Ranges?", _
vbOKCancel Or vbExclamation Or vbDefaultButton1, Application.Name)
Case vbOK
For Each nm In ThisWorkbook.Names
If InStr(1, nm, ActiveSheet.Name) Then
nm.Delete
End If
Next nm
Case vbCancel
Exit Sub
End Select

End Sub