VBA that deletes rows that contain specific text

[fsuguy92]

Hey guys, hoping you can help me! I have 2 VBA's that I need to build that I'm struggling with...

1) VBA will look in column E and will delete all rows that contain specific text (let's say ABC) on a specific worksheet (called "NOT CLEAN")
2) VBA will look at the sum of columns AF:CK and delete all rows where the sum = 0 on a specific worksheet (called "CLEAN")

If you could offer any help I'd appreciate it alot!!

 

[mumps]

This should take care of your first situation:

Sub DeleteRows()
   With Worksheets("NOT CLEAN").Columns("E")
     .Replace "ABC", "#N/A", xlWhole
     .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
   End With
 End Sub

Just to clarify: do you want to add all the cells from AF to CK and if the sum is zero, then delete that row?

 

[fsuguy92]

This should take care of your first situation:

Sub DeleteRows()
   With Worksheets("NOT CLEAN").Columns("E")
     .Replace "ABC", "#N/A", xlWhole
     .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
   End With
 End Sub

Just to clarify: do you want to add all the cells from AF to CK and if the sum is zero, then delete that row?

Thank you! I'm trying that VBA and it doesn't seem to be working, I think it's because ABC is actually the result of a formula?

And yes, I'd like it to look at the sum of AF:CK and if the sum is zero, delete that row.

Thank you!!

 

[mumps]

Assuming you have headers in row 1 and the data starts in row 2, try:

Sub DeleteRowsNC()
    Dim LastRow As Long
    LastRow = Sheets("NOT CLEAN").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With Sheets("NOT CLEAN")
        .Range("E1:E" & LastRow).AutoFilter Field:=1, Criteria1:="ABC"
        .Range("E2:E" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .Range("E1").AutoFilter
    End With
 End Sub

For your second question, try:

Sub DeleteRowsClean()
    Application.ScreenUpdating = False
    Dim LastRow As Long, x As Long, ws As Worksheet
    Set ws = Sheets("CLEAN")
    LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For x = LastRow To 2 Step -1
        If WorksheetFunction.Sum(ws.Range("AF" & x & ":CK" & x)) = 0 Then
            ws.Rows(x).EntireRow.Delete
        End If
    Next x
    Application.ScreenUpdating = True
 End Sub