Macro Using Autofilter in 2 Columns & 1 Criteria Per Col

S

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.
Sort by date Sort by votes
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
 
Upvote 0
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
Click to expand...

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?
 
Upvote 0
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
 
Upvote 0
**************************************************************
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"
 
Upvote 0
**************************************************************
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"
Click to expand...

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
 
Upvote 0
Error 438

Object doens't support this property or method

".AutoFilter"
Click to expand...

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??
Click to expand...
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).
 
Upvote 0
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...
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Workday in VBA
Replies
1
Views
334
Joe4
Joe4
airforceone
  • Solved
AutoFilter Working but not on Date
Replies
5
Views
620
Alex Blakenburg
A
C
  • Question
Adding new date column but excluding weekends
Replies
1
Views
307
Joe4
Joe4
S
  • Question
Can I combine these small macros into one big macro?
Replies
4
Views
373
johnnyL
J
T
  • Solved
AutoFilter Multiple Criteria
Replies
6
Views
896
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,641
Messages
6,120,685
Members
448,978
Latest member
rrauni

Share this page

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
Back
Top