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