Code to delete names works in one WB, but not another WB?

April 28th, 2012 - 02:11 pm ET by hdf | Report spam
I was looking for code to delete cell names within a selected range.
I found the following code which works just fine in the WB I created
to test it, but then won't work in the existing workbook for which I
needed it. It keeps giving a 1004 Run-time error and stops at this
line: " If WithinRange(myCell, myName.RefersToRange) Then".

I have no idea why it works ok in my test WB but not the other one. I
have ensured cells and worksheet are not locked.

Here is the full code - where "TESTRANGE" is the range that contains
the named cells I want to erase. Not all cells within the range are
named.

Sub DeleteNames()

Dim myRange As Range
Dim myCell As Range
Dim myName As Name

Set myRange = Range("TESTRANGE")
For Each myCell In myRange.Cells
For Each myName In ThisWorkbook.Names
If WithinRange(myCell, myName.RefersToRange) Then
myName.Delete
End If
Next myName
Next myCell

End Sub
Function WithinRange(SmallRng, BigRng) As Boolean
' Returns True if smallrng is a subset of Bigrng
WithinRange = False
If SmallRng.Parent.Name = BigRng.Parent.Name Then
If Union(SmallRng, BigRng).Address = BigRng.Address Then
WithinRange = True
End If
End If
End Function
email Follow the discussionReplies 2 repliesReplies Make a reply

Replies

#1 GS
April 28th, 2012 - 07:52 pm ET | Report spam
hdf was thinking very hard :
I was looking for code to delete cell names within a selected range.
I found the following code which works just fine in the WB I created
to test it, but then won't work in the existing workbook for which I
needed it. It keeps giving a 1004 Run-time error and stops at this
line: " If WithinRange(myCell, myName.RefersToRange) Then".

I have no idea why it works ok in my test WB but not the other one. I
have ensured cells and worksheet are not locked.

Here is the full code - where "TESTRANGE" is the range that contains
the named cells I want to erase. Not all cells within the range are
named.

Sub DeleteNames()

Dim myRange As Range
Dim myCell As Range
Dim myName As Name

Set myRange = Range("TESTRANGE")
For Each myCell In myRange.Cells
For Each myName In ThisWorkbook.Names
If WithinRange(myCell, myName.RefersToRange) Then
myName.Delete
End If
Next myName
Next myCell

End Sub
Function WithinRange(SmallRng, BigRng) As Boolean
' Returns True if smallrng is a subset of Bigrng
WithinRange = False
If SmallRng.Parent.Name = BigRng.Parent.Name Then
If Union(SmallRng, BigRng).Address = BigRng.Address Then
WithinRange = True
End If
End If
End Function



See my new reply to your other post...

Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Similar topics