New name range created showing range as '"Sheet2!$A$1" and not Sheet2!$A$1.

[pedie]

hi, myname range created is showing range as '"Sheet2!$A$1" instead of Sheet2!$A$1 when i hit ctrl + f3 and checked it...
I need to name a range for all the words in col a if not blank.

I got this far but names are created with error.

Please advice on how to correct this.
My current code as below.

Sub try()
Dim i As Long
Dim sh As Worksheet
For i = 1 To 200
If Range("A" & i).Value <> "" Then
   Range("A" & i).Select
   ActiveWorkbook.Names.Add Name:=ActiveCell.Value, RefersToR1C1:=ActiveSheet.Name & "!" & ActiveCell.Address
    ActiveWorkbook.Names(ActiveCell.Value).Comment = ""
End If
Next i
End Sub

 

[pedie]

I think this is correct. Looks like it is working

Dim sht As Worksheet
Dim r As Range
For i = 1 To 10
If Range("A" & i).Value <> "" Then
Range("A" & i).Select
Set r = ActiveCell(1, 1)
ActiveWorkbook.Names.Add Name:=ActiveCell.Value, RefersTo:=r
End If
Next i

 

[pgc01]

Hi pedie

You don't usually select objects in vba.

Try, for ex.:

Sub Test()
Dim r As Range
 
For Each r In Range("A1:A10")
    If r.Value <> "" Then ActiveWorkbook.Names.Add Name:=r.Value, RefersTo:=r
Next r
End Sub

 

[pedie]

PGC, cool thanks for suggestion. Your code is more simplier and sure works better as you know better.... "m new learner...!
Months back i guess Norie helped me with something like but cant locate it anymore...so


Thanks again!

One more thing please...
How do i delete all the created name range in active sheet?
Too many name range..i wanna delete all of them.

 

[pgc01]

How do i delete all the created name range in active sheet?

Hi

Try this code to delete all the names that refer to a range in the active sheet:

Sub Test()
Dim nam As Name
Dim r As Range
 
On Error Resume Next
For Each nam In ActiveWorkbook.Names
    Set r = Nothing
    Set r = nam.RefersToRange
    If Not r Is Nothing Then _
        If r.Parent Is ActiveSheet Then _
            nam.Delete
Next nam
End Sub

 

[pedie]

sorry PGC, i think we cross posted. thanks again for the code.

You have a great day!
Bye for now.

 Dim nm As Name
    For Each nm In ActiveWorkbook.Names
        nm.Delete
    Next nm

 

[pgc01]

Hi again

Notice that this last code you posted does not answer your request:

How do i delete all the created name range in active sheet?

The code you posted in post#6 will delete all the names in the workbook, including:

- names that do not refer to ranges
- names that refer to ranges not in the active sheet

which is not what you wanted.