Delete the few whole rows that contains certain text or value for specific sheet

[happymacro]

Hi there.

I am trying to delete any rows that have specific texts such as "car", "labor", "bat" for one of the sheet say sheet2 but my macro doesn't seem to work right. appreciate your help.

Sub deleterow()
For i = last To 1 Step -1
If (Cells(i, "A").Value) = "Labor" Then
Cells(i, "A").EntireRow.Delete

If (Cells(i, "A").Value) = "car" Then
Cells(i, "A").EntireRow.Delete

If (Cells(i, "A").Value) = "bat" Then
Cells(i, "A").EntireRow.Delete

If (Cells(i, "A").Value) = "apple" Then
Cells(i, "A").EntireRow.Delete
End If

Next i

End Sub

 

[wideboydixon]

You've failed to define or set up "last".

Sub DeleteRows()

Dim lastRow As Long
Dim thisRow As Long

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

For thisRow = lastRow To 1 Step -1
    Select Case Cells(thisRow, 1).Value
        Case "Labor", "car", "bat", "apple"
            Cells(thisRow, 1).EntireRow.Delete
    End Select
Next thisRow

End Sub

WBD

 

[Rick Rothstein]

Here is another macro that you can try (your list of words goes in the Array function)...

[table="width: 500"]
[tr]
	[td]Sub DeleteRows()
  Dim V As Variant
  For Each V In Array([B][COLOR="#FF0000"]"car", "labor", "bat"[/COLOR][/B])
    Columns("A").Replace V, "#N/A", xlWhole, , False, , False, False
  Next
  On Error Resume Next
  Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
End Sub[/td]
[/tr]
[/table]

 

[Fluff]

Beaten 2it

 

[happymacro]

You've failed to define or set up "last".

Sub DeleteRows()

Dim lastRow As Long
Dim thisRow As Long

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

For thisRow = lastRow To 1 Step -1
    Select Case Cells(thisRow, 1).Value
        Case "Labor", "car", "bat", "apple"
            Cells(thisRow, 1).EntireRow.Delete
    End Select
Next thisRow

End Sub

WBD

Rick Rothstein & wideboydixon

Thank you! it worked. how about make it work on the specific sheet such as sheet2.

I should add "With worksheets(sheet2"), right?

HM

 

[Rick Rothstein]

Rick Rothstein & wideboydixon
...how about make it work on the specific sheet such as sheet2.

I should add "With worksheets(sheet2"), right?

Yes, and put a dot before each cell or range that should reference that sheet. Here is my code with the With statement added...

[table="width: 500"]
[tr]
	[td]Sub DeleteRows()
  Dim V As Variant
  With Sheets("Sheet2")
    For Each V In Array("car", "labor", "bat")
      .Columns("A").Replace V, "#N/A", xlWhole, , False, , False, False
    Next
    On Error Resume Next
    .Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
  End With
End Sub[/td]
[/tr]
[/table]

 

[happymacro]

Hi Rick:

how can I also add another function if columns contain certain text strings (labor cost) for example "ABC Labor cost" and "DEF labor cost"? In addition, I coded to copy the original sheet and how can I re-name the macro sheet into the different sheet name?


Sub delete entire row


Dim Sitedata As Worksheet
Set Sitedata = ThisWorkbook.Worksheets("Sitedata")
Sitedata.Copy ThisWorkbook.Sheets(Sheets.Count)




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"
Cells(thisRow, 1).EntireRow.Delete

End Select

Next thisRow


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

 

[happymacro]

You've failed to define or set up "last".

Sub DeleteRows()

Dim lastRow As Long
Dim thisRow As Long

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

For thisRow = lastRow To 1 Step -1
    Select Case Cells(thisRow, 1).Value
        Case "Labor", "car", "bat", "apple"
            Cells(thisRow, 1).EntireRow.Delete
    End Select
Next thisRow

End Sub

WBD

how can I also add another function if columns contain certain text strings (labor cost) for example "ABC Labor cost" and "DEF labor cost"? In addition, I coded to copy the original sheet and how can I re-name the macro sheet into the different sheet name?


Sub delete entire row


Dim Sitedata As Worksheet
Set Sitedata = ThisWorkbook.Worksheets("Sitedata")
Sitedata.Copy ThisWorkbook.Sheets(Sheets.Count)




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"
Cells(thisRow, 1).EntireRow.Delete

End Select

Next thisRow


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

 

[rholdren]

I've been trying to delete rows based on the content of column F. There are many departments in my company and I want to delete all those one name at a time. for example if what is in column f is <> Account Management then delete all the other departments and keep account management. When i have if equal to it does nothing. If I do <> it deletes all the other departments first then goes back and deletes the account management last leaving me with a blank page. Any help would be greatly appreciated. Thanks

Sub ACCT_MGMT()
'
Dim LR As Long

For LR = Range("f" & Rows.Count).End(xlUp).Row To 2 Step -1
If Range("f" & LR).Value <> "Account Mangement" Then
Rows(LR).EntireRow.Delete



End If
Next LR




'
End Sub