Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Can I Loop through all the range names in a workbook in VBA?
Hi, I am using Excel2002 SP3
I need to identify all the range names within a workbook Any ideas? My below code does not work error message 'argument not optional' Dim ws As Worksheet Dim rng As Range For Each ws In ActiveWorkbook.Worksheets For Each rng In ws.Range Select Case rng.Name Case "rangename" do soemthing Case Else End Select Next Next |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Can I Loop through all the range names in a workbook in VBA?
Try this:
Sub IterateNames() Dim ws As Worksheet Dim nm As Name For Each ws In ActiveWorkbook.Worksheets For Each nm In ws.Names Select Case nm.Name Case "rangename" 'do soemthing Case Else Debug.Print nm.Name End Select Next Next End Sub Darren Kay wrote: Hi, I am using Excel2002 SP3 I need to identify all the range names within a workbook Any ideas? My below code does not work error message 'argument not optional' Dim ws As Worksheet Dim rng As Range For Each ws In ActiveWorkbook.Worksheets For Each rng In ws.Range Select Case rng.Name Case "rangename" do soemthing Case Else End Select Next Next |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Can I Loop through all the range names in a workbook in VBA?
Sub name_it()
Dim n As Name Dim r As Range Dim s As String For Each n In ThisWorkbook.Names MsgBox (n.Name) s = Range(n).Address MsgBox (s) Next n End Sub -- Gary''s Student - gsnu2007a "Kay" wrote: Hi, I am using Excel2002 SP3 I need to identify all the range names within a workbook Any ideas? My below code does not work error message 'argument not optional' Dim ws As Worksheet Dim rng As Range For Each ws In ActiveWorkbook.Worksheets For Each rng In ws.Range Select Case rng.Name Case "rangename" do soemthing Case Else End Select Next Next |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Can I Loop through all the range names in a workbook in VB
Have you tried Insert-Names-paste list? BE SURE TO SELECT A LARGE RANGE
OF EMPTY CELLS FIRST "Darren Hill" wrote: Try this: Sub IterateNames() Dim ws As Worksheet Dim nm As Name For Each ws In ActiveWorkbook.Worksheets For Each nm In ws.Names Select Case nm.Name Case "rangename" 'do soemthing Case Else Debug.Print nm.Name End Select Next Next End Sub Darren Kay wrote: Hi, I am using Excel2002 SP3 I need to identify all the range names within a workbook Any ideas? My below code does not work error message 'argument not optional' Dim ws As Worksheet Dim rng As Range For Each ws In ActiveWorkbook.Worksheets For Each rng In ws.Range Select Case rng.Name Case "rangename" do soemthing Case Else End Select Next Next |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Can I Loop through all the range names in a workbook in VBA?
Thanks Guys
Both worked - should have been looking at Name rather than Range |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting email address names in a range of cells to real names | Excel Worksheet Functions | |||
how to make range names universal in workbook | Excel Discussion (Misc queries) | |||
Names referring to valid range in the active workbook | Excel Programming | |||
how to copy workbook names and worksheet names to columns in acces | Excel Programming | |||
Copying Range Names to another open workbook | Excel Programming |