PDA

View Full Version : Solved: Deleting Defined Names



Opv
07-30-2010, 11:16 AM
The following code deletes all defined names in a worksheet. I'm curious as to whether there is a way to modify this to make it work only on names defined in the heading row, i.e., Rows(2)?


Sub test()

Dim n As Name

For Each n In Names
n.Delete
Next

End Sub

Simon Lloyd
07-30-2010, 12:57 PM
Try:
Sub test()
Dim n As Name, oCell As Range, Rng As Range
Set Rng = Range(Range("A2"), Cells(2, Rows("2:2").End(xlToRight).Column))
For Each n In Names
For Each oCell In Rng
on error resume next
If oCell.Value = n.Name Then
n.Delete
End If
Next oCell
Next n
End Sub

p45cal
07-30-2010, 01:39 PM
Perhaps a little shorter, if I've understood the question properly (I thought at first it meant defined names whose ranges included or were entirely in row 2):Sub blah()
For Each cll In Union(Rows(2).SpecialCells(xlCellTypeConstants, 23), Rows(2).SpecialCells(xlCellTypeFormulas, 3)).Cells
On Error Resume Next
Names(cll.Value).Delete
Next cll
On Error GoTo 0
End Sub

mikerickson
07-30-2010, 11:54 PM
Sub test()
Dim oneName As Name
For Each oneName In ThisWorkbook.Names
On Error Resume Next
If oneName.RefersToRange.Row <> 2 Then
Rem do nothing
Else
oneName.Delete
End If
On Error GoTo 0
Next oneName
End Sub

Opv
07-31-2010, 07:05 AM
Thanks everyone. Simon, the suggested code only removed one of the names in the heading row. p45cal, I received an error when I tried the suggested code. Mike, your suggestion does the trick. I had already been experimenting with the "RefersToRange" piece but hadn't yet figured out how to make it work.

Thanks again to all for the help.

Opv

p45cal
07-31-2010, 07:16 AM
If oneName.RefersToRange.Row <> 2 Then
Rem do nothing
Else
oneName.Delete
End If mikerickson, why did you go for the above instead of the likes of:If oneName.RefersToRange.Row = 2 Then oneName.Delete?

mikerickson
07-31-2010, 01:00 PM
If oneName does not refer to a Range, oneName.RefersTo range will error.
The OnError Resume Next protects against that.
let oneName ReferTo the formula ="x"


On Error Resume Next
If oneName.RefersToRange.Row <> 2 Then :Rem code errors, resume with next line

Rem Do Nothing: Rem don't delete this name

Else: Rem equivilant to "GoTo End If"

oneName.Delete: Rem will execute iff .RefersTo does not error and .Row<>2

End If
On Error Goto 0
If the Else branch were more complicated, I might have reset On Error Goto 0 inside the branches.

You are right, the block If construction is not needed, so I could have done it all in one line (wrapped in Error handling)