title: Delete rows based on values of two columns
body: <p>I want to retain any rows in excel which only contain "ECGS2A" or "ECGS2B" in column E and "Customer Opt In" in column M but having difficulty with trying different VBA codes.</p>
  
  <p>I need to retain headers on row 4 but when trying to add range other than column E on:</p>
  
  <pre><code>j = Range("E" &amp; Rows.Count).End(xlUp).Row
  </code></pre>
  
  <p>I get an error or "Run time error '1004': Method of 'Range' of object_Global' failed</p>
  
  <pre><code>' Deleting entire rows with MyTarget
  Sub myDeleteRows2()
  
  Const MyTarget = "*ECGS2A*"
  
  Dim Rng As Range, DelCol As New Collection, x
  Dim I As Long, j As Long, k As Long
  
  ' Calc last row number
  j = Range("E" &amp; Rows.Count).End(xlUp).Row
  
  ' Collect rows range with MyTarget
  For I = 1 To j
  If WorksheetFunction.CountIf(Rows(I), MyTarget) = 0 Then 'changed from &gt; 0
  k = k + 1
  If k = 1 Then
  Set Rng = Rows(I)
  Else
  Set Rng = Union(Rng, Rows(I))
  If k &gt;= 100 Then
  DelCol.Add Rng
  k = 0
  End If
  End If
  End If
  Next
  If k &gt; 0 Then DelCol.Add Rng
  
  ' Turn off screen updating and events
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  
  ' Delete rows with MyTarget
  For Each x In DelCol
  x.Delete
  Next
  
  
  ' Update UsedRange
  With ActiveSheet.UsedRange: End With
  
  ' Restore screen updating and events
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  
  End Sub
  </code></pre>
  
  <p>also tried</p>
  
  <pre><code>Sub DeleteRowsBasedOnMultipleCriteria()
  
  lRow = 13 ' Your last row with the data
  
  Do While lRow &gt;= 1
  
  '1=Column A,6=Column F, 18=Column R
   If Cells(lRow, 5) = "ECGS9" _
          Or Cells(lRow, 13) = "Customer Opt Out" Then
          Rows(lRow).Delete
   End If
  
  lRow = lRow - 1
  Loop
  
  End Sub
  </code></pre>
  
  <p>I expect to be left with column E only displaying anything with ECGS2A or ECGS2B and column M as Customer Opt In.  If the columns display anything other than those mentioned, I want them deleted.</p>
  
  <p><img src="https://i.stack.imgur.com/2bvDr.png" alt="enter image description here"></p>