Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Excel > Autofilter with multiple columns and multiple criteria


Reply
 
Thread Tools Display Modes
  #1  
Old 07-06-2005, 08:11 AM
elrao elrao is offline
Freshman
 
Join Date: Dec 2003
Posts: 25
Question 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?
Reply With Quote
  #2  
Old 07-06-2005, 12:01 PM
italkid's Avatar
italkid italkid is offline
Down...

Retired Moderator
* Expert *
 
Join Date: Dec 2002
Location: Belgium.
Posts: 6,731
Default

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
Reply With Quote
  #3  
Old 07-08-2005, 05:02 AM
elrao elrao is offline
Freshman
 
Join Date: Dec 2003
Posts: 25
Default

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
Reply With Quote
  #4  
Old 07-08-2005, 11:21 AM
italkid's Avatar
italkid italkid is offline
Down...

Retired Moderator
* Expert *
 
Join Date: Dec 2002
Location: Belgium.
Posts: 6,731
Default

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
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->