Macro Using Autofilter in 2 Columns & 1 Criteria Per Col

Superstar31

Active Member
Joined
Nov 10, 2005
Messages
496
OK, I've made a macro that needs to autofilter different columns one column has numbers the other has letters

Selection.AutoFilter
Selection.AutoFilter Field:=9, Criteria1:="9546", Operator:=xlAnd, _
Selection.AutoFilter Field:=5, Criteria2:="RT"
Cells.Select

Is there a proper way to do this?

Thanks
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Each filter has separate criteria, so the second filter should be Criteria1, not 2. Also, you can use a With statement here:

Code:
With Selection
     .AutoFilter
     .AutoFilter Field:=9, Criteria1:="9546"
     .AutoFilter Field:=5, Criteria1:="RT"
End With

Cells.Select will select ALL cells on the worksheet. If you're only wanting to select the cells after the filter runs, you can use the SpecialCells property to select only those cells that are visible at the time:
Code:
Cells.SpecialCells(xlCellTypeVisible).Select
 

Superstar31

Active Member
Joined
Nov 10, 2005
Messages
496
Each filter has separate criteria, so the second filter should be Criteria1, not 2. Also, you can use a With statement here:

Code:
With Selection
     .AutoFilter
     .AutoFilter Field:=9, Criteria1:="9546"
     .AutoFilter Field:=5, Criteria1:="RT"
End With

Cells.Select will select ALL cells on the worksheet. If you're only wanting to select the cells after the filter runs, you can use the SpecialCells property to select only those cells that are visible at the time:
Code:
Cells.SpecialCells(xlCellTypeVisible).Select

Sub LCDM_Vendor_Reports()
'
' LCDM_Vendor_Reports Macro
' Macro recorded 9/28/2006 by Marc Miller
'

'
Sheets("Data").Select
Columns("E:G").Select
Selection.Delete Shift:=xlToLeft
Range("A1:I1").Select
With Selection
.AutoFilter
.AutoFilter Field:=9, Criteria1:="9546"
.AutoFilter Field:=5, Criteria1:="RT"
End With
Cells.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("9546").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Data").Select
Selection.AutoFilter Field:=9, Criteria1:="9551"
Selection.AutoFilter Field:=5
Application.CutCopyMode = False
Selection.Copy
Sheets("9551").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1:I1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Cells.Select
Selection.ColumnWidth = 44
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit
Selection.AutoFilter Field:=5, Criteria1:="RT"
Sheets("9546").Select
Cells.Select
Selection.ColumnWidth = 38.57
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit
Sheets("Main").Select
End Sub

Like that?
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
You tell me; did it work? I have no idea, it's your workbook.

Incidentally, you do not (always) have to select sheets/cells to work with them. Just referencing them directly can cut down on the amount of code you use and in some cases the amount of time it takes to run. Cleaning up the code you posted a bit, I end up with this (Note: this is completely untested):

Code:
Sub LCDM_Vendor_Reports()

With Sheets("Data")
    .Columns("E:G").Delete Shift:=xlToLeft
    
    'turn on autofilter if it is not already active
    If .AutoFilterMode = False Then .AutoFilter
    
    .AutoFilter Field:=9, Criteria1:="9546"
    .AutoFilter Field:=5, Criteria1:="RT"
    .Cells.SpecialCells(xlCellTypeVisible).Copy
    Sheets("9546").Cells.PasteSpecial Paste:=xlPasteValues
    
    .AutoFilter Field:=9, Criteria1:="9551"
    .AutoFilter Field:=5
    .Cells.SpecialCells(xlCellTypeVisible).Copy
End With
With Sheets("9551")
    With .Cells
        .PasteSpecial Paste:=xlPasteValues
        .EntireColumn.AutoFit
        .EntireRow.AutoFit
    End With
    If .AutoFilterMode = False Then .AutoFilter
    .AutoFilter Field:=5, Criteria1:="RT"
End With
With Sheets("9546").Cells
    .EntireColumn.AutoFit
    .EntireRow.AutoFit
End With

Sheets("Main").Select

End Sub
 

Superstar31

Active Member
Joined
Nov 10, 2005
Messages
496

ADVERTISEMENT

**************************************************************
Sub LCDM_Vendor_Reports()

With Sheets("Data")
.Columns("E:G").Delete Shift:=xlToLeft

'turn on autofilter if it is not already active
If .AutoFilterMode = False Then .AutoFilter

.AutoFilter Field:=9, Criteria1:="9546"
.AutoFilter Field:=5, Criteria1:="RT"
.Cells.SpecialCells(xlCellTypeVisible).Copy
Sheets("9546").Cells.PasteSpecial Paste:=xlPasteValues

.AutoFilter Field:=9, Criteria1:="9551"
.AutoFilter Field:=5
.Cells.SpecialCells(xlCellTypeVisible).Copy
End With
With Sheets("9551")
With .Cells
.PasteSpecial Paste:=xlPasteValues
.EntireColumn.AutoFit
.EntireRow.AutoFit
End With
If .AutoFilterMode = False Then .AutoFilter
.AutoFilter Field:=5, Criteria1:="RT"
End With
With Sheets("9546").Cells
.EntireColumn.AutoFit
.EntireRow.AutoFit
End With

Sheets("Main").Select

End Sub

*************************************************************
Error 438

Object doens't support this property or method

".AutoFilter"
 

Superstar31

Active Member
Joined
Nov 10, 2005
Messages
496
**************************************************************
Sub LCDM_Vendor_Reports()

With Sheets("Data")
.Columns("E:G").Delete Shift:=xlToLeft

'turn on autofilter if it is not already active
If .AutoFilterMode = False Then .AutoFilter

.AutoFilter Field:=9, Criteria1:="9546"
.AutoFilter Field:=5, Criteria1:="RT"
.Cells.SpecialCells(xlCellTypeVisible).Copy
Sheets("9546").Cells.PasteSpecial Paste:=xlPasteValues

.AutoFilter Field:=9, Criteria1:="9551"
.AutoFilter Field:=5
.Cells.SpecialCells(xlCellTypeVisible).Copy
End With
With Sheets("9551")
With .Cells
.PasteSpecial Paste:=xlPasteValues
.EntireColumn.AutoFit
.EntireRow.AutoFit
End With
If .AutoFilterMode = False Then .AutoFilter
.AutoFilter Field:=5, Criteria1:="RT"
End With
With Sheets("9546").Cells
.EntireColumn.AutoFit
.EntireRow.AutoFit
End With

Sheets("Main").Select

End Sub

*************************************************************
Error 438

Object doens't support this property or method

".AutoFilter"

The difference I noticted between your code and mine is the indents, is that important?? also the 1st code seemed to have worked, I started fresh and it worked for the most part, still have to fine tune it on my end
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Error 438

Object doens't support this property or method

".AutoFilter"

Ah--looks like I forgot to put in the range. Try this in place of the FIRST instsance of this line:

Code:
If .AutoFilterMode = False Then .Range("A1:I1").AutoFilter

he difference I noticted between your code and mine is the indents, is that important??
The indents are not necessary for the code to *work*, no. They just make it a heck of a lot easier to read/follow (using the code tags when posting macro code on the board helps, too).
 

Superstar31

Active Member
Joined
Nov 10, 2005
Messages
496
I ended up using your first example, since I still had a couple of tweaks.... Thank you very much, because it did what I indended...
 

Watch MrExcel Video

Forum statistics

Threads
1,113,800
Messages
5,544,347
Members
410,605
Latest member
hinspate
Top