I have a simple macro that deletes a workbook scoped named range with
the name "test". The macro seems to work in most cases except when
the workbook also contains a worksheet scoped named range with the
same name.
Steps to reproduce:
1. Create a new workbook
2. Go to Sheet2, select several cells, and create a named range with
the name "Sheet2!test" without the quotes (the scope of this named
range is of the worksheet)
3. Go to Sheet1, select several cells, and create a named range with
the name "name" without the quotes (the scope of this named range is
of the workbook)
4. Create and run the macro below
If the macro is run from Sheet1, the workbook scoped named range,
"name", is deleted, which is the desired behavior. If the macro is
run from Sheet2, the worksheet scoped name range "Sheet2!test" is
deleted, which is not desired.
How do I delete a specified workbook scoped named range in all cases?
Sub DeleteWorkbookNamedRange()
Dim i As Integer
Dim strName As String
For i = 1 To Application.Names.Count
strName = Application.Names.Item(i).Name
If strName = "test" Then
Application.Names.Item(i).Delete
End If
Next i
End Sub
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
