add names
 

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.

add names
 

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/23/2008 by Joel Warburg
'

'
For RowCount = 1 To 35
ActiveWorkbook.Names.Add Name:=Range("A" & RowCount), _
RefersTo:="=" & Range("B" & RowCount)
Next RowCount
End Sub

For Each nm In ActiveWorkbook.Names
nm.Delete
Next nm


"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.

add names
 

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