Hello,

I have an excel file where I am trying to filter range of rows based on a macro but get an error: 'AutoFilter mehod of Range class failed'. Below is the macro code I am using:

Sub Button1_Click()

With ActiveSheet()
.AutoFilterMode = False
.Range("B12:B48").AutoFilter
.Range("B12:B48").AutoFilter Field:=2, Criteria1:=Range("B12:B48").Value

End With
E

Re:  problem with autofilter

With ActiveSheet()
 .AutoFilterMode = False
 .Range("B12:B48").AutoFilter
 .Range("B12:B48").AutoFilter Field:=1, Criteria1:="juicy"
End With

-or-

With ActiveSheet
 .AutoFilterMode = False
 .Range("B12:B48").AutoFilter , Field:=1, Criteria1:="juicy"
End With

'---
Jim Cone
Portland, Oregon USA
free & commercial excel programs  (n/a xl2013)
https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2
-or-
http://jmp.sh/K95N3ee

I want to indeed put a criteria for a whole range of rows fom B12:B48 regardless of the data

Re:  problem with autofilter

With ActiveSheet()
 .AutoFilterMode = False
 .Range("B12:B48").AutoFilter
 .Range("B12:B48").AutoFilter Field:=1, Criteria1:="juicy"
End With

-or-

With ActiveSheet
 .AutoFilterMode = False
 .Range("B12:B48").AutoFilter , Field:=1, Criteria1:="juicy"
End With

'---
Jim Cone
Portland, Oregon USA
free & commercial excel programs  (n/a xl2013)
https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2
-or-
http://jmp.sh/K95N3ee

• Proposed as answer by Vish Mishra 22 hours 48 minutes ago

Re:  problem with autofilter

With ActiveSheet()
 .AutoFilterMode = False
 .Range("B12:B48").AutoFilter
 .Range("B12:B48").AutoFilter Field:=1, Criteria1:="juicy"
End With

-or-

With ActiveSheet
 .AutoFilterMode = False
 .Range("B12:B48").AutoFilter , Field:=1, Criteria1:="juicy"
End With

'---
Jim Cone
Portland, Oregon USA
free & commercial excel programs  (n/a xl2013)
https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2
-or-
http://jmp.sh/K95N3ee

• Proposed as answer by Vish Mishra Saturday, June 13, 2015 8:42 AM

Re:  problem with autofilter

With ActiveSheet()
 .AutoFilterMode = False
 .Range("B12:B48").AutoFilter
 .Range("B12:B48").AutoFilter Field:=1, Criteria1:="juicy"
End With

-or-

With ActiveSheet
 .AutoFilterMode = False
 .Range("B12:B48").AutoFilter , Field:=1, Criteria1:="juicy"
End With

'---
Jim Cone
Portland, Oregon USA
free & commercial excel programs  (n/a xl2013)
https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2
-or-
http://jmp.sh/K95N3ee

• Proposed as answer by Vish Mishra Saturday, June 13, 2015 8:42 AM

Hi, 

James has exactly given you the same code which will apply an autofilter and condition on whole range (B12:B48) regardless of data.

if, your query is answered kindly mark, above answer as "Answer"

Hi,

This is the forum to discuss questions and feedback for Microsoft Excel, if you need further help about coding, I recommend you post the question to the MSDN forum for Excel

http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

George Zhao
TechNet Community Support