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

 

[gordsky]

do you want the colC delete to occur IF the ColB condition is met or independant of colB

 

[thomasuponor]

do you want the colC delete to occur IF the ColB condition is met or independant of colB

Hi, to be independant.

 

[gordsky]

Maybe something like this would work for you?

Sub MyFilter()

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

    Set ws = ActiveWorkbook.Sheets("Bilag_1")
    Set rng = ws.UsedRange
    
    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
  
    With rng
        .AutoFilter Field:=2, Operator:=xlFilterValues, Criteria1:=Array("MLC", "PEX")
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
  
    ws.AutoFilterMode = False
End Sub

 

[thomasuponor]

Maybe something like this would work for you?

Sub MyFilter()

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

    Set ws = ActiveWorkbook.Sheets("Bilag_1")
    Set rng = ws.UsedRange
   
    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
 
    With rng
        .AutoFilter Field:=2, Operator:=xlFilterValues, Criteria1:=Array("MLC", "PEX")
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
 
    ws.AutoFilterMode = False
End Sub

Thanks for the contribution. Unfortunately it didn't work. No rows are being deleted.

 

[gordsky]

do you have header rows or blank rows above the data?? If so what row does your data start on?

 

[thomasuponor]

do you have header rows or blank rows above the data?? If so what row does your data start on?

I've uploaded image. Just noticed that "PEX" and "MLC" are in merged cells. But not sure that make a difference.

 

[gordsky]

I've uploaded image. Just noticed that "PEX" and "MLC" are in merged cells. But not sure that make a difference.

OK so what exactly is it you are trying to do. I get the Colb you want to get rid of entire row.
When it comes to the PEX & MLC are you trying to get rid of the data UNDER them (as if Pex was a sub heading for example) or are you literally trying to just remove the row with PEX & MLC in it and leave the data below them in tact?

 

[thomasuponor]

OK so what exactly is it you are trying to do. I get the Colb you want to get rid of entire row.
When it comes to the PEX & MLC are you trying to get rid of the data UNDER them (as if Pex was a sub heading for example) or are you literally trying to just remove the row with PEX & MLC in it and leave the data below them in tact?

I would like the macro to check column B and Column A (Headlines).

In column B, if the values "1033080", "1033081", etc. are there THEN delete row

Also
In Column A (Headlines) if "PEX", "MLC" are there THEN delete row

Hope it makes sense.

 

[gordsky]

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