Delete Rows using Autofilter

**maacmaac** · 01-06-2012, 10:14 AM

This is a two part question. (1) I am trying to delete rows if it equals one of 4 predfined values. The code below works if I am only using two values but not working for 4. It stops when the code gets to the "apply filter" section. (2) the other question is I also want to do the opposite of number one...i.e. instead of Criteria1:=DeleteValue1, I would want to use Criteria1:<>DeleteValue1 (not equal to DeleteValue). Full sample attached if needed.

    DeleteValue1 = "Z007"
    DeleteValue2 = "AXAB"
    DeleteValue3 = "TEST"
    DeleteValue4 = "TE2T"

    With ActiveSheet

        'Remove the AutoFilter
        .AutoFilterMode = False

        'Apply the filter
        .Range("K1:K" & .Rows.Count).AutoFilter Field:=1, _
        Criteria1:=DeleteValue1, Operator:=xlOr, _
        Criteria2:=DeleteValue2, Operator:=xlOr, _
        Criteria3:=DeleteValue3, Operator:=xlOr, _
        Criteria4:=DeleteValue4

        With .AutoFilter.Range
            On Error Resume Next
            Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                      .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            If Not rng Is Nothing Then rng.EntireRow.Delete
        End With

**snb** · 01-06-2012, 10:17 AM

In autofilter you can only use two criteria at a time.
So you'll have to filter twice.

sub snb()
  sn=split("Z007|AXAB|TEST|TE2T","|")

 With ActiveSheet.columns(11)
  c01=.cells(1).value
  .specialcells(4).value=" "

  .autofilter 1,sn(0) ,xlor,sn(1)
  .SpecialCells(12).clearcontents
  .autofilter
    
  .autofilter 1,sn(2) ,xlor,sn(3)
  .SpecialCells(12).clearcontents
  .autofilter

  .specialcells(4).entirerow.delete
  replace " ","",1
  .cells(1).value=c01
 end with
end sub

**maacmaac** · 01-06-2012, 10:42 AM

Snb, the code is deleting all rows. I only want to delete the row if the cell in column K is equal to one of the four values. Otherwise do nothing. Thanks.

**snb** · 01-06-2012, 10:51 AM

I amended the code.

**maacmaac** · 01-06-2012, 11:09 AM

Thanks. Were you going to attach a spreadsheet? I didn't see any new code in your comments. Thanks.

**maacmaac** · 01-06-2012, 01:33 PM

snb, I see you posted the amended code in your previous post. I did try the new code. Is is working except for one issue. When I run the code, it is deleting all the headers. Is there a way to avoid having the headers being deleted (see attached). Thanks.

**snb** · 01-06-2012, 02:30 PM

swapping lines....

sub snb()
  sn=split("Z007|AXAB|TEST|TE2T","|")

 With ActiveSheet.columns(11)
  c01=.cells(1).value
  .specialcells(4).value=" "

  .autofilter 1,sn(0) ,xlor,sn(1)
  .SpecialCells(12).clearcontents
  .autofilter
    
  .autofilter 1,sn(2) ,xlor,sn(3)
  .SpecialCells(12).clearcontents
  .autofilter

  .cells(1).value=c01
  .specialcells(4).entirerow.delete
  replace " ","",1
 end with
end sub