This adds the names as global names (workbook level):
Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim TestRng As Range
With Worksheets("sheet1")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With
For Each myCell In myRng.Cells
myCell.Offset(0, 2).Value = "ok"
Set TestRng = Nothing
On Error Resume Next
Set TestRng = Application.Range(myCell.Offset(0, 1).Value)
On Error GoTo 0
If TestRng Is Nothing Then
myCell.Offset(0, 2).Value = "Invalid address!"
Else
On Error Resume Next
TestRng.Name = myCell.Value
If Err.Number < 0 Then
myCell.Offset(0, 2).Value = "Invalid name!"
Err.Clear
End If
On Error GoTo 0
End If
Next myCell
End Sub
And this would delete the names:
Option Explicit
Sub testme02()
Dim myName As Name
Dim myRng As Range
Dim myCell As Range
With Worksheets("sheet1")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With
For Each myCell In myRng.Cells
On Error Resume Next
ThisWorkbook.Names(myCell.Value).Delete
If Err.Number < 0 Then
myCell.Offset(0, 3).Value = "error while deleting"
Err.Clear
Else
myCell.Offset(0, 3).Value = "Deleted"
End If
Next myCell
End Sub
Since you're working with names, get Jan Karel Pieterse's (with Charles Williams
and Matthew Henson) Name Manager:
You can find it at:
NameManager.Zip from
http://www.oaltd.co.uk/mvp
You'll find it very valuable.
Scott wrote:
I have to define a few dozens of names in an Excel files. I put all the names
in Range ("A1:A35") and their respective addresses in Range ("B1:B35"), such
as "sheet1!$a$1:$c$20". What is the macro to run this job?
Also, want to know what are the codes to delete all these names later on if
necessary.
Thanks.
--
Dave Peterson