Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a name for a range pointing to "this"?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a name for a range pointing to "this"?
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 -- Gary "Maury Markowitz" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
use variable in Workbooks("book1").Worksheets("sheet1").Range("a1" | Excel Programming | |||
Finding the current "print range" | Excel Programming | |||
Using "Cells" to write "Range("A:A,H:H").Select" | Excel Programming | |||
Pointing search function to "This Drive"? | Excel Programming |