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

happymacro

New Member
Joined
Nov 4, 2017
Messages
18
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
You've failed to define or set up "last".

Code:
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
 
Upvote 0
Here is another macro that you can try (your list of words goes in the Array function)...
Code:
[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]
 
Last edited:
Upvote 0
Beaten 2it
 
Last edited:
Upvote 0
You've failed to define or set up "last".

Code:
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
 
Upvote 0
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...
Code:
[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]
 
Upvote 0
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
 
Upvote 0
You've failed to define or set up "last".

Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,198
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top