Solved VBA code for Autofilter with 2 criteria

July 24, 2018 at 07:46:36
Specs: Windows 10
I'm trying to do a simple copy and paste of a worksheet into a new workbook and then apply an autofilter that would filter out data on one or another criteria.

The data is pasting fine into a new workbook, but the code I have for the autofilter keeps giving me a compile error: expected list separator or ). I've tried playing around with the range where I've coded a specific range, a whole column etc but having googled extensively I can't see what's wrong with what I've written:

Sub CopyBenefits()

'Copy the Data worksheet to a new workbook
Worksheets("Data").Copy

'Apply an autofilter to just get rows that have a status of "Completed" or "Completed pending confirmation"
ActiveSheet.Range(“F:F").AutoFilter Field:=14, Criteria1:=”=Completed”, Operator:=xlOr, _
Criteria2:="=Completed pending confirmation"

End Sub

The column headers are pasted into row 2 of the new workbook and start from column F. The filter is to be applied to column S which is called "Status" I also ideally want to express the range for the filter as F2: the last row on the spreadsheet, but haven't got past the issue above yet so not worked out the code for the range.

Thanks

message edited by ScottV


See More: VBA code for Autofilter with 2 criteria

Reply ↓  Report •

#1
July 24, 2018 at 10:30:34
EDIT: Please read this response for background, but my next response may be more of what you are looking for.

*****************************************************************************

OK, a couple of things.

When I pasted your AutoFilter instruction into the VBE, the entire instruction turned red, meaning that there is a syntax error. Further investigation revealed that some of the quotation marks are the wrong style. I think they are those MS Smart Quotes from Word or something like that. Note the difference in the quotation marks, as copied directly from your post:

“F:F"

I had to replace all the (smart quotes) with " (straight quotes) to satisfy the VBE.

With that fix out of the way, I'll first mention that I do not do a lot of coding related to filters, so when a Filtering question is asked, I just do some testing to see what I can get to work. In this case I created a test sheet to filter against and tried your code. VBA threw up this error:

"AutoFilter method of range class failed"

What I discovered was that unless the Filter had already been applied to Columns "F:S" before running the code, the Range("F:F") reference was the cause of the error. If I changed the code to Range("F:S") and ran it, the code applied the filter. I could then change the code back to Range("F:F") and it would work fine. The other option was to physically select Columns F:S in the worksheet and click the Filter button to apply the filter. Once that was done the Range("F:F") reference worked fine.

In other words, the filter had to be applied to the Range("F:S"), either manually or via the code, in order for the Range("F:F") reference to work.

I don't know if that is of any help to you, but once I applied both of those "fixes" (the quotation mark and the pre-filtering) your instruction worked fine.

Let me know if that helps.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#2
July 24, 2018 at 14:02:18
✔ Best Answer
re: "I also ideally want to express the range for the filter as F2: the last row on the spreadsheet"

I think this may solve most of your issues. The quotes are fixed, the last Row with data is determined and the filter can be applied to a fresh worksheet.

Sub FilterRange()

'Determine lastRow in Column S
  lastRw = Cells(Rows.Count, "S").End(xlUp).Row

'Apply an autofilter to just get rows that have a status of "Completed" or "Completed pending confirmation"
   ActiveSheet.Range("F2:S" & lastRw).AutoFilter Field:=14, Criteria1:="=Completed", Operator:=xlOr, _
   Criteria2:="=Completed pending confirmation"
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#3
July 25, 2018 at 07:49:35
Great spot about the quote marks. That was driving me nuts because I was pretty sure I had the syntax right.

Once I changed the code, including your second post above it worked perfectly.

Thank you as always. Much appreciated.


Reply ↓  Report •

Related Solutions

#4
July 25, 2018 at 09:50:06
I'm glad I could help, but I'm also curious.

How did the different types of quotation marks end up in the instruction? I've seen cases where all of the quotation marks were smart quotes, but never a mix-and-match situation like yours.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#5
July 26, 2018 at 00:21:16
It's actually not so mysterious when I think about it. After googling I copied the ActiveSheet.Range bit of the code and then started playing around with different ranges. I think I must have left the initial quote marks in place and just kept deleting and trying different ranges to no avail. So I would have replaced the final quote marks without touching the first ones.

Mystery solved. :-)


Reply ↓  Report •

Ask Question