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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

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...
 

Forum statistics

Threads
1,141,012
Messages
5,703,730
Members
421,312
Latest member
Mooncake1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top