Delete the whole rows that contains certain words and exact words in the column

[happymacro]

Hi there:

I tried to delete the whole row if the column contains certain words for example if a column contain "Labor Cost", I want to delete the entire row. The macro I have only can delete exact words that I put in but I would really like to delete both exact words and contain certain words.

Here is what I have but not sure where went wrong.

Dim lastRow As Long
Dim thisRow As Long


Sheets("Sitedata").Select


lastRow = Cells(Rows.Count, 1).End(xlUp).Row


For thisRow = lastRow To 1 Step -1


Select Case Cells(thisRow, 1).Value
Case "Bat", "Apple", Order ID", "Orange"
Cells(thisRow, 1).EntireRow.Delete

End Select

Next thisRow


Sheets("Sitedata").Select


lastRow = Cells(Rows.Count, 1).End(xlUp).Row
For thisRow = lastRow To 1 Step -1




If Cells(thisRow, 1).Value = "LABOR Cost" Then
Cells(thisRow, 1).EntireRow.Delete




End If




Next thisRow




End Sub

 

[mrshl9898]

Try changing your words from "Bat" to "*Bat*" etc.

 

[Rick Rothstein]

Give this macro a try...

Sub DeleteWholeRowsForCertainWords()
  Dim V As Variant
  For Each V In Array("Bat", "Apple", "Order ID", "Orange")
    Columns("A").Replace V, "#N/A", xlPart, , False, , False, False
  Next
  On Error GoTo NoSuchWords
  Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
NoSuchWords:
End Sub

Note: I am a little concerned about false positive matches. For example, you are looking for the word "bat", but the code I posted would also flag words like "battle", "combat", etc.

 

[happymacro]

Try changing your words from "Bat" to "*Bat*" etc.

It did not work though.

 

[happymacro]

No loop needed for this coding?

Give this macro a try...

Sub DeleteWholeRowsForCertainWords()
  Dim V As Variant
  For Each V In Array("Bat", "Apple", "Order ID", "Orange")
    Columns("A").Replace V, "#N/A", xlPart, , False, , False, False
  Next
  On Error GoTo NoSuchWords
  Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
NoSuchWords:
End Sub

Note: I am a little concerned about false positive matches. For example, you are looking for the word "bat", but the code I posted would also flag words like "battle", "combat", etc.

 

[Rick Rothstein]

No loop needed for this coding?

There is a For Each loop in my code, but only of the words you are searching for... Excel's Replace functionality can handle multiple cells at at once as can SpecialCells.

 

[happymacro]

Hi Rich:

It worked partially and I want to remove the entire row whenever the column contain these words.

For example if a phase { ABC "Labor Cost"}, this phase contains "labor cost", I want the whole {ABC Labor Cost} row column deleted.

The codes you wrote here replace labor cost to #N/A. how can I revise this?

There is a For Each loop in my code, but only of the words you are searching for... Excel's Replace functionality can handle multiple cells at at once as can SpecialCells.

 

[Rick Rothstein]

Hi Rich:

It worked partially and I want to remove the entire row whenever the column contain these words.

For example if a phase { ABC "Labor Cost"}, this phase contains "labor cost", I want the whole {ABC Labor Cost} row column deleted.

The codes you wrote here replace labor cost to #N/A. how can I revise this?

Does this revised code do what you want...

Sub DeleteWholeRowsForCertainWords()
  Dim V As Variant
  For Each V In Array("Bat", "Apple", "Order ID", "Orange")
    Columns("A").Replace "*" & V & "*", "#N/A", xlWhole, , False, , False, False
  Next
  On Error GoTo NoSuchWords
  Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
NoSuchWords:
End Sub

 

[happymacro]

WOW!!!!!! Perfect! Thank you so much, Rick.

Does this revised code do what you want...

Sub DeleteWholeRowsForCertainWords()
  Dim V As Variant
  For Each V In Array("Bat", "Apple", "Order ID", "Orange")
    Columns("A").Replace "*" & V & "*", "#N/A", xlWhole, , False, , False, False
  Next
  On Error GoTo NoSuchWords
  Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
NoSuchWords:
End Sub

 

[happymacro]

Rick,

One last question,

How can I code to "copy the original sheet (before running the macro) and rename the worksheet (after running the macro) into "Macro-ed"?

WOW!!!!!! Perfect! Thank you so much, Rick.