VBA - Delete rows based on values from two columns (delete when just one value is met)

[thomasuponor]

Hi,

I have below macro that looks into column B and deletes row if one of the numbers are present.
I would want the macro to include also column C and delete the rows if these criteria are present - "MLC", "PEX"

Have tried to add an extra Set rng = ws.Range("C3:C" & lastRow) but does not work.

Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long

    Set ws = ActiveWorkbook.Sheets("Bilag_1")

    lastRow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row

    Set rng = ws.Range("B3:B" & lastRow)

    ' filter and delete all but header row
    With rng
        .AutoFilter Field:=1, Operator:=xlFilterValues, Criteria1:=Array("1033080", "1033081", "1033179", "1033277", "1033084", "1033186", "1033270", "1033086", "1059576", "1059577")
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    ' turn off the filters
    ws.AutoFilterMode = False

 

[thomasuponor]

Try this

Sub Myfilter()
   
  Dim ws As Worksheet
  Dim rng As Range, myPEX As Range, myMLC As Range
  Dim lastRow As Long
   
  Set ws = ActiveWorkbook.Sheets("Bilag_1")
  Set myPEX = ws.UsedRange.Find("PEX", LookIn:=xlValues)
  Set myMLC = ws.UsedRange.Find("MLC", LookIn:=xlValues)
    
    lastRow = ws.Range("B" & ws.Rows.count).End(xlUp).row
   
  Set rng = ws.Range("B3:B" & lastRow)
   
  If Not myPEX Is Nothing Then
      ws.Rows(myPEX.row).Delete
  End If

  If Not myMLC Is Nothing Then
      ws.Rows(myMLC.row).Delete
  End If
   
  ws.AutoFilterMode = False
   
' filter and delete all but header row
  With rng
      .AutoFilter Field:=1, Operator:=xlFilterValues, Criteria1:=Array("1033080", "1033081", "1033179", "1033277", "1033084", "1033186", "1033270", "1033086", "1059576", "1059577")
      .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
  End With

' turn off the filters
   ws.AutoFilterMode = False
 
End Sub

Great job - THX!

 

[gordsky]

Glad I could help