remove unwanted items in column

[EDUCATED MONKEY]

I am attempting to modiffy this code for my own needs can some one tell me what I am doing wrong, I thought I was get the hang of this but clearly I have some way to go yet as I thought all I had to do was refference the worksheet supply the item to be deleted but no go it wrong some how below marked in red is what I expected to be delete althou most of you can run rings around me I very greatfull for the code but feel that I need no just to copy and use it need to learn form you what I am doing wrong so that I can aviod it in future
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
<o> </o>
<o> </o>
Sub DeleteDeadNames()<o></o>
'<o></o>
' DeleteDeadNames Macro<o></o>
'<o></o>
' Keyboard Shortcut: Ctrl+Shift+D<o></o>
Dim nName As Name<o></o>
<o> </o>
Worksheets("REMOVE UNWANTED ITEMS").Select<o></o>
Columns("A:A").Select<o></o>
<o></o>
<o> </o>
For Each nName In Names<o></o>
<o> </o>
If InStr(1, nName.RefersTo, "First Plays") > 0 Then<o></o>
<o> </o>
nName.DELETE<o></o>
<o> </o>
End If<o></o>
<o> </o>
Next nName<o></o>
<o> </o>
End Sub<o></o>
<o> </o>
<o> </o>
<TABLE style="MARGIN: auto auto auto 5.4pt; WIDTH: 237.8pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=317><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 237.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=317 noWrap>Chloe Marr <o></o>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 237.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=317 noWrap>Christopher Robin's Old Sailor: And Other Selections <o></o>
</TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 237.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 13.5pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=317 noWrap>Collections<o></o>
</TD></TR><TR style="HEIGHT: 14.25pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 237.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: red; HEIGHT: 14.25pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=317 noWrap>First Plays <o></o>
</TD></TR><TR style="HEIGHT: 13.5pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 237.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 13.5pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=317 noWrap>For the Luncheon Interval <o></o>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 5"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 237.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=317 noWrap>If I May <o></o>
</TD></TR><TR style="HEIGHT: 16.5pt; mso-yfti-irow: 6"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 237.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 16.5pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=317 noWrap>Its Super to be Six! <o></o>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 7; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; PADDING-BOTTOM: 0cm; PADDING-LEFT: 5.4pt; WIDTH: 237.8pt; PADDING-RIGHT: 5.4pt; BACKGROUND: lime; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0; PADDING-TOP: 0cm" vAlign=bottom width=317 noWrap>Knights of Madness: Further Comic Tales of Fantasy <o></o>
</TD></TR></TBODY></TABLE>
<o> </o>

 

[Ralajer]

The above code loops through all the named ranges in the workbook looking for a particular string in the refersTo property of each named range, deleting the named range when a match is found. I just want to make sure I understand what you actually want to do because the code works to do what I described above.

Could you explain what you need more explicitly.
Example: Code needs to delete the entire row of any cell in Column A on Worksheet REMOVE UNWANTED ITEMS that contains the string First Plays.

 

[Rick Rothstein]

Does this code do what you want?

Sub DeleteText()
  Dim N As Name
  Const TextToDelete As String = "First Plays"
  On Error Resume Next
  For Each N In ThisWorkbook.Names
    N.RefersToRange.Replace TextToDelete, "=" & TextToDelete, xlWhole
    N.RefersToRange.SpecialCells(xlCellTypeFormulas).Delete
  Next
End Sub

By the way, the code I posted should work no matter how many cells are in your named ranges (your posted code seems to indicate they might be one cell each) and no matter whether the cells are contiguous or not.