Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

AutoFilter multiple criteria

  Asked By: Cathy    Date: Mar 13    Category: MS Office    Views: 971
  

I have a spreadsheet with 8 columns and a varying number of rows
(between 160 and 250). My macro creates 2 simple pivot tables based
on the data in columns B and C. I use AutoFilter to select the
information I need based on the data in column A. Part of the code
(that works fine) is:-

******

Worksheets("Order").Select
Cells.Select
Worksheets("Order").AutoFilterMode = False
Selection.AutoFilter Field:=1, Criteria1:="=z*"

With ActiveSheet.AutoFilter.Range
Set rngPreviousOrder = .Offset(1, 1).Resize(.Rows.Count - 1, 2) _
.SpecialCells(xlCellTypeVisible)
End With

rngPreviousOrder.AdvancedFilter xlFilterCopy, , Worksheets
("SortedList") _
.Range("A2")

******

This filters out anything in column A starting with z and copies it
to my sheet "SortedList". The next filter should copy everything
except those that start with "z" and those that equal "stock". The
code for this is identical except for the AutoFilter which sets 2
criteria and the destination which starts at cell D2 as below:-

******

Worksheets("Order").Select
Cells.Select
Worksheets("Order").AutoFilterMode = False
Selection.AutoFilter Field:=1, Criteria1:="<>z*", Operator:=xlAnd,
_
Criteria2:="<>stock"

With ActiveSheet.AutoFilter.Range
Set rngCurrentOrder = .Offset(1, 1).Resize(.Rows.Count - 1, 2) _
.SpecialCells(xlCellTypeVisible)
End With

rngCurrentOrder.AdvancedFilter xlFilterCopy, , Worksheets
("SortedList") _
.Range("D2")

******

Sorry if the formatting looks odd - I don't know what it will look
like when you see it but it is OK here. This may not look pretty but
I have cobbled this together from recording macros and info from the
net over a period of 2 months - I have had no training in VBA. The
macro fails at the second xlFilterCopy with the message "Run-time
error 1004: Database or list range is not valid", yet the criteria
fields are exactly as recorded in macro recorder. If I change the
operator to xlOr instead of xlAnd the macro finishes but it copies
everything instead of what I want.

Any help would be appreciated (as would any tips regarding my
coding) and if I haven't explained this fully, please let me know.

Share: 

 

No Answers Found. Be the First, To Post Answer.

 
Didn't find what you were looking for? Find more on AutoFilter multiple criteria Or get search suggestion and latest updates.




Tagged: