Macro for deleting rows based on two criteria

[jesuscares]

Hello guys

I am in need of your assistance. I have spreadsheet with 10 columns (i.e A to J). In column A, I need to remove any entries with the term "FILE" and in column C, any entries with the term "NEWS". The end result of this process should be removal of the associated rows. At the moment, I have setup the following code to remove only the rows based on column A containing entries with the term "FILE". It is as follows:

Sub Sample()
Dim ws As Worksheet
Dim lRow As Long
Dim strSearch As String

'~~> Set this to the relevant worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")

'~~> Search Text
strSearch = "FILE"

With ws
'~~> Remove any filters
.AutoFilterMode = False

lRow = .Range("A" & .Rows.Count).End(xlUp).Row

With .Range("A1:A" & lRow)
.AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

'~~> Remove any filters
.AutoFilterMode = False
End With
End Sub

My question is therefore, how should I amend the existing code to include the removal of the term "NEWS" in column C? Ideally, I would like for both processes to occur concurrently so that my output would reflect the removal of the rows with both terms. I greatly appreciate any help!

 

[Trebor76]

Hi jesuscares,

Welcome to MrExcel!!

Try this:

Option Explicit
Sub Macro1()

    Const lngStartRow As Long = 2 'Starting row number for the data. Change to suit.
    
    Dim lngMyCol As Long, _
        lngMyRow As Long
    Dim xlnCalcMethod As XlCalculation
            
    With Application
        xlnCalcMethod = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    lngMyCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
    lngMyRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    With Columns(lngMyCol)
        With Range(Cells(lngStartRow, lngMyCol), Cells(lngMyRow, lngMyCol))
            .Formula = "=IF(AND(A" & lngStartRow & "=""FILE"",C" & lngStartRow & "=""NEWS""),NA(),"""")"
            ActiveSheet.Calculate
            .Value = .Value
        End With
        On Error Resume Next 'Turn error reporting off - OK to ignore 'No cells found' message
            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
        On Error GoTo 0 'Turn error reporting back on
        .Delete
    End With
    
    With Application
        .Calculation = xlnCalcMethod
        .ScreenUpdating = True
    End With

    MsgBox "All rows that had ""FILE"" and ""NEWS"" in columns A and C respectively have now been deleted.", vbInformation

End Sub

Note that the code is not case sensitive i.e. any row(s) with the text "file" and "news" will be deleted. I'd also initially try it on a sample of your data as results cannot be undone if they're not as expected.

Regards,

Robert

 

[Rick Rothstein]

I am pretty sure this macro will work for you as well...

Sub Sample2()
  On Error Resume Next
  Columns("A").Replace "FILE", "#N/A", xlPart, , False
  Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  Columns("C").Replace "NEWS", "#N/A", xlPart, , False
  Columns("C").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
End Sub

 

[jesuscares]

I am pretty sure this macro will work for you as well...

Sub Sample2()
  On Error Resume Next
  Columns("A").Replace "FILE", "#N/A", xlPart, , False
  Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  Columns("C").Replace "NEWS", "#N/A", xlPart, , False
  Columns("C").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
End Sub

Thanks Rick. This seemed to do the trick!! For future reference, is there a way to remove the rows with cells containing the word "FILE" as part of string of words for example "FILE CLOSED" or "FILE EMPTTY"?

 

[Rick Rothstein]

Thanks Rick. This seemed to do the trick!! For future reference, is there a way to remove the rows with cells containing the word "FILE" as part of string of words for example "FILE CLOSED" or "FILE EMPTTY"?

Yes, surround the word with asterisks...

Columns("A").Replace "[B][COLOR="#FF0000"]*[/COLOR][/B]FILE[B][COLOR="#FF0000"]*[/COLOR][/B]", "#N/A", xlPart, , False