Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Names referring to valid range in the active workbook
hello,
I'm looking for a procedure that identifies Names referring to valid range in the active workbook (and excludes all names referring to external workbooks or to formula or to constants....) Please help Thanks Avi |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Names referring to valid range in the active workbook
Hi avi.
Try: Public Sub Macro1() Selection.ListNames End Sub Regards Eliano "avi" wrote: hello, I'm looking for a procedure that identifies Names referring to valid range in the active workbook (and excludes all names referring to external workbooks or to formula or to constants....) Please help Thanks Avi |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Names referring to valid range in the active workbook
The procedure returns all the names while i'm interested in only valid
ranges in the active workbook Thanks Avi |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Names referring to valid range in the active workbook
avi,
Try this With ActiveWorkbook Selection.ListNames End With "avi" wrote: The procedure returns all the names while i'm interested in only valid ranges in the active workbook Thanks Avi |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Names referring to valid range in the active workbook
Sub test2()
Dim cnt As Long Dim wb As Workbook Dim nm As Name Dim rng As Range Dim arrNames() Set wb = ActiveWorkbook cnt = wb.Names.Count If cnt = 0 Then Exit Sub ReDim arrNames(1 To cnt, 1 To 2) cnt = 0 On Error Resume Next For Each nm In wb.Names Set rng = Range(nm.Name) If Not rng Is Nothing Then If rng.Parent.Parent Is wb Then cnt = cnt + 1 arrNames(cnt, 1) = nm.Name arrNames(cnt, 2) = "' " & nm.RefersTo End If Set rng = Nothing Else Err.Clear End If Next If cnt Then Range("A1").Resize(cnt, 2).Value = arrNames End If End Sub This will also include formula type names that indirectly refer to a range in the workbook. It will exclude range names that refer to a range in another workbook, and other formula or non range names. Regards, Peter T "avi" wrote in message ups.com... hello, I'm looking for a procedure that identifies Names referring to valid range in the active workbook (and excludes all names referring to external workbooks or to formula or to constants....) Please help Thanks Avi |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Names referring to valid range in the active workbook
Peter T's code seems to do what you need, but your workbook might
contain named ranges for single worksheets. So you should add something like for each ws in wb.worksheets for each nm in ws.names .... next nm next ws to your code. Peter T wrote: Sub test2() Dim cnt As Long Dim wb As Workbook Dim nm As Name Dim rng As Range Dim arrNames() Set wb = ActiveWorkbook cnt = wb.Names.Count If cnt = 0 Then Exit Sub ReDim arrNames(1 To cnt, 1 To 2) cnt = 0 On Error Resume Next For Each nm In wb.Names Set rng = Range(nm.Name) If Not rng Is Nothing Then If rng.Parent.Parent Is wb Then cnt = cnt + 1 arrNames(cnt, 1) = nm.Name arrNames(cnt, 2) = "' " & nm.RefersTo End If Set rng = Nothing Else Err.Clear End If Next If cnt Then Range("A1").Resize(cnt, 2).Value = arrNames End If End Sub This will also include formula type names that indirectly refer to a range in the workbook. It will exclude range names that refer to a range in another workbook, and other formula or non range names. Regards, Peter T "avi" wrote in message ups.com... hello, I'm looking for a procedure that identifies Names referring to valid range in the active workbook (and excludes all names referring to external workbooks or to formula or to constants....) Please help Thanks Avi |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Names referring to valid range in the active workbook
Worksheet level names also exist in the workbook's Names collection, where
they are listed like this - SheetName!LocalName So for the purposes of this exercise it shouldn't be necessary to loop worksheets and do 'for each nm in ws.names', unless of course the OP requires that additional local/global info about the name. Regards, Peter T "Luca Brasi" wrote in message ... Peter T's code seems to do what you need, but your workbook might contain named ranges for single worksheets. So you should add something like for each ws in wb.worksheets for each nm in ws.names .... next nm next ws to your code. Peter T wrote: Sub test2() Dim cnt As Long Dim wb As Workbook Dim nm As Name Dim rng As Range Dim arrNames() Set wb = ActiveWorkbook cnt = wb.Names.Count If cnt = 0 Then Exit Sub ReDim arrNames(1 To cnt, 1 To 2) cnt = 0 On Error Resume Next For Each nm In wb.Names Set rng = Range(nm.Name) If Not rng Is Nothing Then If rng.Parent.Parent Is wb Then cnt = cnt + 1 arrNames(cnt, 1) = nm.Name arrNames(cnt, 2) = "' " & nm.RefersTo End If Set rng = Nothing Else Err.Clear End If Next If cnt Then Range("A1").Resize(cnt, 2).Value = arrNames End If End Sub This will also include formula type names that indirectly refer to a range in the workbook. It will exclude range names that refer to a range in another workbook, and other formula or non range names. Regards, Peter T "avi" wrote in message ups.com... hello, I'm looking for a procedure that identifies Names referring to valid range in the active workbook (and excludes all names referring to external workbooks or to formula or to constants....) Please help Thanks Avi |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Names referring to valid range in the active workbook
your right! damn, i coded too much lines for years.... :(
Peter T wrote: Worksheet level names also exist in the workbook's Names collection, where they are listed like this - SheetName!LocalName So for the purposes of this exercise it shouldn't be necessary to loop worksheets and do 'for each nm in ws.names', unless of course the OP requires that additional local/global info about the name. Regards, Peter T "Luca Brasi" wrote in message ... Peter T's code seems to do what you need, but your workbook might contain named ranges for single worksheets. So you should add something like for each ws in wb.worksheets for each nm in ws.names .... next nm next ws to your code. Peter T wrote: Sub test2() Dim cnt As Long Dim wb As Workbook Dim nm As Name Dim rng As Range Dim arrNames() Set wb = ActiveWorkbook cnt = wb.Names.Count If cnt = 0 Then Exit Sub ReDim arrNames(1 To cnt, 1 To 2) cnt = 0 On Error Resume Next For Each nm In wb.Names Set rng = Range(nm.Name) If Not rng Is Nothing Then If rng.Parent.Parent Is wb Then cnt = cnt + 1 arrNames(cnt, 1) = nm.Name arrNames(cnt, 2) = "' " & nm.RefersTo End If Set rng = Nothing Else Err.Clear End If Next If cnt Then Range("A1").Resize(cnt, 2).Value = arrNames End If End Sub This will also include formula type names that indirectly refer to a range in the workbook. It will exclude range names that refer to a range in another workbook, and other formula or non range names. Regards, Peter T "avi" wrote in message ups.com... hello, I'm looking for a procedure that identifies Names referring to valid range in the active workbook (and excludes all names referring to external workbooks or to formula or to constants....) Please help Thanks Avi |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Names referring to valid range in the active workbook
Great!
Thaks a lot for your detailed help Avi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula referring to a dynamic range in a different workbook | Excel Worksheet Functions | |||
Sumif referring to range names formulas not updating | Excel Worksheet Functions | |||
Range selecting in non-active workbook | Excel Programming | |||
hidden names in active workbook | Excel Worksheet Functions | |||
Get range value active workbook on open add-in | Excel Programming |