VBA to find Value, then Name Cell

[OldDogNewTricks]

I want my code to do two things.
1) Locate the cells in Column A that have a certain value
2) Name the cells with that certain value

All the values will be in Column A. The word I am searching for will appear three different times in the column. I need a code that will find those cells and name them. I have tried multiple different angles, and I am not sure where I am going wrong. I greatly appreciate any help you can provide!

I have Excel 2007.

Example:
The following is supposed to represent Column A in my document. I am searching for cells that have the word "Bacon" in it. The code should name the first cell containing the word, "Bacon1." The next cell containing the word should be named "Bacon2." This continues until no more cells containing "Bacon" can be found and labeled.

Toppings
Bacon
Hamburger
Chicken
Peppers
Cheese
Meats
Bacon
Hamburger
Chicken
Extras
Sauce
Cheese
Bacon

 

[OldDogNewTricks]

I keep getting runtime error 1004 when I add that line of code. Any other ideas?

 

[Rick Rothstein]

I keep getting runtime error 1004 when I add that line of code. Any other ideas?

I may have misread your question... are you asking to erase the name for a specific row or for all the rows? Also, what version of Excel are you using?

 

[OldDogNewTricks]

I am using Excel 2007. I want to erase the names in every row in column A of my worksheet, so I do not run into issues where a row has a name from a previous time I ran the code. I can only figure out how to erase all the names in the workbook.

 

[Rick Rothstein]

I am using Excel 2007. I want to erase the names in every row in column A of my worksheet, so I do not run into issues where a row has a name from a previous time I ran the code. I can only figure out how to erase all the names in the workbook.

You can use this macro to delete all the named rows on the worksheet...

Sub DeleteNames()
  Dim N As Name
  On Error Resume Next
  For Each N In ActiveWorkbook.Names
    If Range(Mid(N.RefersTo, 2)).Columns.Count = Columns.Count Then N.Delete
  Next N
  On Error GoTo 0
End Sub

 

[OldDogNewTricks]

This works beautifully. Thank you so much for helping me!

 

[OldDogNewTricks]

The previous code for deleting the row names does not work like I thought it had. However, I found this in the Mr. Excel archives, and it works for deleting name ranges for a single sheet:

Dim n As Name
Dim Sht As String
' Put in name of sheet where the range is located
Sht = "Sheet2"

For Each n In ActiveWorkbook.Names
If n.RefersToRange.Worksheet.Name = Sht Then
n.Delete
End If
Next n

 

[Rick Rothstein]

The previous code for deleting the row names does not work like I thought it had. However, I found this in the Mr. Excel archives, and it works for deleting name ranges for a single sheet:

Dim n As Name
Dim Sht As String
' Put in name of sheet where the range is located
Sht = "Sheet2"

For Each n In ActiveWorkbook.Names
If n.RefersToRange.Worksheet.Name = Sht Then
n.Delete
End If
Next n

Let's combine the relevant part of the above code with the code I posted to make sure the only defined names that get deleted are the ones that refer to whole rows on the sheet. And note that I changed the hard-coded sheet name to the active worksheet so that you can run this code on any sheet without having to change the name assigned to a variable each time... just activate the sheet whose whole-row defined names you want to delete and run the code.

Sub DeleteNames()
  Dim N As Name
  On Error Resume Next
  For Each N In ActiveWorkbook.Names
    If N.RefersToRange.Worksheet.Name = ActiveSheet.Name Then
      If Range(Mid(N.RefersTo, 2)).Columns.Count = Columns.Count Then N.Delete
    End If
  Next N
  On Error GoTo 0
End Sub