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

 

[Rick Rothstein]

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

Does this modified version of my code do what you want...

Sub DeleteWholeRowsForCertainWords()
  Dim V As Variant, CurrentSheet As Worksheet
  Set CurrentSheet = ActiveSheet
  CurrentSheet.Copy After:=CurrentSheet
  ActiveSheet.Name = "Macro-ed"
  CurrentSheet.Activate
  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]

HI Rick:

Happy Friday. the macro you provided worked very well. Is there any limitation how many words or phrase we can put into Array( "Bat", "Apple").

I tried to put like 130 words and I no longer to put more in the (). If there is a limitation, how can I expend to more than 200 or 300 words?

HM

Does this modified version of my code do what you want...

Sub DeleteWholeRowsForCertainWords()
  Dim V As Variant, CurrentSheet As Worksheet
  Set CurrentSheet = ActiveSheet
  CurrentSheet.Copy After:=CurrentSheet
  ActiveSheet.Name = "Macro-ed"
  CurrentSheet.Activate
  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