Editing Macro in VBA AutoFilter Array error

Question

I'm editing a document with 2500 entries and need to apply an AutoFilter, and this is my first try at editing the macro code, please have patience.

I used record to create the macro, and when I went to edit parts of it I got "Compile error: Expected: expression" outside the portion of the code I edited. the '...' is many more entries.

ActiveSheet.Range("$A$1:$C$2500").AutoFilter Field := 2, Criteria1 := Array( _
    "#N/A","3K Technologies, LLC","ABN","Accenture","AIMCo",...,"ITAT Partners" _
    ,"IT-Beratung Gunnar Hofmann","John Hancock","John Hancock Financial Network", _  
End Sub

Not all the entries are shown and all of the code above except "End Sub" is in red.

It seems that since I recorded the macro, as opposed to writing it, all the name values are being stored and causing a problem. How do I apply an auto filter to a large selection of cells in VBA? Are the existing company names causing an overflow?

Thank you in advance!

Really briefly, without actually looking at your problem (I'll get there), I presume it's not the spaces before the :=? — mkingston
– mkingston, Commented Aug 7, 2012 at 22:34
when you editied your code, you also removed the end of the array... The _ at the end of the 3rd line makes vba think that the End Sub is part of the Array( — SeanC
– SeanC, Commented Aug 8, 2012 at 13:55

danielpiestrak · Accepted Answer · 2012-08-08 00:00:57Z

1

If you just want to APPLY an Autofilter you can just use:

ActiveSheet.Range("$A$1:$C$2500").AutoFilter

All of the rest of your code is all of the settings you recorded like each check-box for each column.

If you're trying to edit the default selection settings in that stuff after the filter is applied, which section are you having trouble with in particular?

One thing to note is that Excel's VBA can only continue the same line of code down around 24 times with the _ symbol at the end. Excel will fail to record the filter's checkbox selections if there are too many so you'll have to code them in by hand if this is, in fact, the specific problem you are having then follow this setup:

With ActiveSheet.Range("$A$1:$C$2500")
    '- Use only one line break with a long list off to the right of what you want selected
    '- it may look ugly in code, but with Excel's limit of 24 lines it's necissary in this case.
    .AutoFilter Field:=2, _
        Criteria1:=Array("#N/A", "3K Technologies, LLC", "ABN", "Accenture", "AIMCo", "ITAT Partners", "IT-Beratung Gunnar Hofmann", "John Hancock", "John Hancock Financial Network")
    '- Here you can add settings for additional columns like this:
    '.AutoFilter Field:=1, _
    '    Criteria1:=Array("#N/A", "3K Technologies, LLC", "ABN", "Accenture", "AIMCo", "ITAT Partners", "IT-Beratung Gunnar Hofmann", "John Hancock", "John Hancock Financial Network")
End With

edited Aug 8, 2012 at 0:00

answered Aug 7, 2012 at 23:44

danielpiestrak

5,4793 gold badges33 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

angs Over a year ago

Thank you for the insight, I am going to work with it and see what I can get from this myself. Good stuff to know, thanks again.

Collectives™ on Stack Overflow

Editing Macro in VBA AutoFilter Array error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related