Deleting Row based on cell value with VBA

[iaman44]

Hello, I am trying to eliminate rows that have a certain word in them.

All of the words are in the same two columns. In this case either B or F. So, if the word appears in column B or F I would like the code to identify the word and then delete the row.

I am intermediate level user and haven't found anything that works. I appreciate any help. I can add sample data, but it really is as straight forward as the word yellow appears in column B row 20 and column F row 45 and I want code that can identify yellow in those two places and delete row 20 and 45.

Thanks in advance.

 

[RileyC]

Do you need a code that does it? It wouldn't be too hard to simply make an excel table, undo the word "yellow" on the table, then you have a table without it inside of it. Otherwise lets say you have a list of words defined:

Sub deleteRowWithBadWord()
    Dim listOfBannedWords as Collection : Set listOfBannedWords = new Collection
    Dim tableToUse as Range : Set tableToUse = ThisWorkbook.Worksheets("YourNameHere").Range("RangeToUse")

    listOfBannedWords.Add "yellow"
    listOfBannedWords.Add "red"
    listOfBannedWords.Add "blue"

    For i = 1 to tableToUse.Rows("ColumnToCheck").Count
         For j = 1 to listOfBannedWords.Count
             If tableToUse.Cells(i,"ColumnToCheck").value2 = listOfBannedWords.Item(j) Then
                 tableToUse.Range(i,"ColumnToCheck").Delete
                 Exit For
             End If
         Next j
    Next i
End Sub

For more help learning how the tables and range work I'd recommend the following links:
https://excelmacromastery.com/excel-vba-range-cells/
https://docs.microsoft.com/en-us/office/vba/api/excel.range(object)

 

[Rick Rothstein]

Here is another macro that you can consider (just keep adding your words to the list in the Array function call)...

[table="width: 500"]
[tr]
	[td]Sub DeleteRowIfBorFhasWordInIt()
  Dim W As Variant, Words As Variant
  Words = Array("Yellow", "Red", "Blue")
  For Each W In Words
    Range("B:B,F:F").Replace W, "#N/A", xlWhole, , False, , False, False
  Next
  On Error Resume Next
  Columns("B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  Columns("F").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
End Sub[/td]
[/tr]
[/table]

 

[iaman44]

Here is another macro that you can consider (just keep adding your words to the list in the Array function call)...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub DeleteRowIfBorFhasWordInIt()
  Dim W As Variant, Words As Variant
  Words = Array("Yellow", "Red", "Blue")
  For Each W In Words
    Range("B:B,F:F").Replace W, "#N/A", xlWhole, , False, , False, False
  Next
  On Error Resume Next
  Columns("B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  Columns("F").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

This worked beautifully. One last issue. I want it to work on 2 specific worksheets in a workbook with about 25 worksheets. None of the things I know how to have a macro work on a specific worksheet is working.

I would imagine the answer is quite simple, but the stuff I've tried gets me an error debug message, or it doesn't do anything, and the macro acts on the page I am on, regardless if I want it to run on that page or not.

Once again, I appreciate any help anyone can give.

 

[iaman44]

Forget it. I figured it out. Easiest answer was correct one, I was just making a dumb mistake.

Thank you to Rick Rothstein for your well crafted solution to my problem. RileyC as well. It also works great.

Thank you both!!

 

[Rick Rothstein]

This worked beautifully. One last issue. I want it to work on 2 specific worksheets in a workbook with about 25 worksheets. None of the things I know how to have a macro work on a specific worksheet is working.

Change my example sheet names to your actual sheet names...

[table="width: 500"]
[tr]
	[td]Sub DeleteRowIfBorFhasWordInIt()
  Dim W As Variant, Words As Variant, WS As Worksheet
  Words = Array("Yellow", "Red", "Blue")
  For Each WS In Sheets(Array("[B][COLOR="#FF0000"]Sheet2[/COLOR][/B]", "[B][COLOR="#FF0000"]Sheet6[/COLOR][/B]"))
    For Each W In Words
      WS.Range("B:B,F:F").Replace W, "#N/A", xlWhole, , False, , False, False
    Next
    On Error Resume Next
    WS.Columns("B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    WS.Columns("F").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
  Next
End Sub[/td]
[/tr]
[/table]