Solved: Deleting Defined Names

[Opv]

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

[VBA]
Sub test()

Dim n As Name

For Each n In Names
n.Delete
Next

End Sub
[/VBA]

[Simon Lloyd]

Try:
[vba]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
[/vba]

[p45cal]

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):[vba]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
[/vba]

[mikerickson]

[VBA]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[/VBA]

[Opv]

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]

[vba]
If oneName.RefersToRange.Row <> 2 Then
Rem do nothing
Else
oneName.Delete
End If[/vba]

mikerickson, why did you go for the above instead of the likes of:[vba]If oneName.RefersToRange.Row = 2 Then oneName.Delete[/vba]?

[mikerickson]

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)