Autofilter with multiple columns and multiple criteria

07-06-2005, 07:11 AM

elrao

Freshman

Join Date: Dec 2003

Posts: 25

Autofilter with multiple columns and multiple criteria

Hi

I have a sheet of data, which I need to clear up. What I want to do is use the autofilter function to filter out the information that I need, leaving what I don't need visible and then delete the visible rows.

First filter is on column D, where I need to select all rows that are in "Unit1" and then filter on column C to select all rows that are NOT "Team1" or "Team2".

Code I have is:

vb/
Dim CheckRange As Range, DeleteRange As Range

Set CheckRange = Worksheets("Sheet1").Columns(7)
CheckRange.AutoFilter Field:=4, Criteria1:="Unit1"
CheckRange.AutoFilter Field:=3, Criteria1:="<>Team1", Operator:=xlOr, Criteria2:="<>Team2"

Set DeleteRange = CheckRange.SpecialCells(xlCellTypeVisible).EntireRow
DeleteRange.Delete
/vb

However this does not work. It filters out all the non "Unit1" rows fine, but then applied a filter to Column C that does nothing!

Can someone help put me out of my misery?

07-06-2005, 11:01 AM

italkid

Down...

Retired Moderator
* Expert *

Join Date: Dec 2002

Location: Belgium.

Posts: 6,731

You ask for filtering column 7 first (a single column)...
Made some minor changes:

Code:

Dim DeleteRange As Range

With Worksheets("Sheet1")
  .Cells.AutoFilter Field:=4, Criteria1:="Unit1"
  .Cells.AutoFilter Field:=3, Criteria1:="<>Team1", Operator:=xlOr, Criteria2:="<>Team2"

  Set DeleteRange = .AutoFilter.Range.SpecialCells(xlCellTypeVisible)
  
  DeleteRange.EntireRow.Delete
End With

Not tested but should work

07-08-2005, 04:02 AM

elrao

Freshman

Join Date: Dec 2003

Posts: 25

Thanks

Managed to get it to work with a minor modification, appears as though what I thought said "Team1" didn't say "Team1", I had to use "Team1*" to get it to work.

However it deletes my autofilter / header row. I know i need to do something with offset to get this to work, but not sure what.

How do i modify this bit to make it not delete my header row?

Code:

Set DeleteRange = .AutoFilter.Range.SpecialCells(xlCellTypeVisible)
  
  DeleteRange.EntireRow.Delete

07-08-2005, 10:21 AM

italkid

Down...

Retired Moderator
* Expert *

Join Date: Dec 2002

Location: Belgium.

Posts: 6,731

Change that part of your code into:

Code:

With Worksheets("Sheet1")
  'filter stuff here and then...
  
  With .AutoFilter.Range
    Set DeleteRange = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
  End With
  
  DeleteRange.EntireRow.Delete
End With