+ Reply to Thread
Results 1 to 4 of 4

Macro with autofilter with an array to remove unwanted criteria with wildcard

  1. #1
    Registered User
    Join Date
    04-28-2014
    Location
    Denver, CO
    MS-Off Ver
    Excel 2010
    Posts
    4

    Macro with autofilter with an array to remove unwanted criteria with wildcard

    I'm having a hard time making this maro work in Excel 2010.

    I need it to filter out the items "AR", "BATCH", and the line of "Total:*" where the * is a total amount of any given number dependant on the day.

    Below is the coding I have that Excel is not liking. Please assist if possible.

    Thanks,
    Crystal


    Sub FilterAccurateRawData()
    '
    ' FilterAccurateRawData Macro
    '

    '
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$AA$45415").AutoFilter Field:=1, Criteria1:=Array("<>AR", "<>BATCH", "<>Total:*")
    Operator:=xlFilterValues
    Sheets("Instructions").Select
    Range("A9").Select
    End Sub

  2. #2
    Registered User
    Join Date
    04-28-2014
    Location
    Denver, CO
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Macro with autofilter with an array to remove unwanted criteria with wildcard

    This is giving me a syntax error, but I can't find the error.

  3. #3
    Registered User
    Join Date
    04-28-2014
    Location
    Denver, CO
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Macro with autofilter with an array to remove unwanted criteria with wildcard

    when I modify it, it says:
    Compile error: expected expression on the Operator:=xlFilterValues

  4. #4
    Registered User
    Join Date
    04-28-2014
    Location
    Denver, CO
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Macro with autofilter with an array to remove unwanted criteria with wildcard

    I have modified the syntax to work properly finally, but the autofilter is not eliminating "AR" and "BATCH" and "Total*" from the filter selection. It is unselecting everything and allowing everything through.

    here is the new syntax.

    Sub FilterAccurateRawData()
    '
    ' FilterAccurateRawData Macro
    '

    '
    Sheets("Accurate Raw Data").Select
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    Range("A1:AA1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$AA$45415").AutoFilter Field:=1, Criteria1:=Array("<>AR", _
    "<>BATCH"), _
    Operator:=xlAnd
    'ActiveSheet.Range("$A$1:$AA$45415").AutoFilter Field:=1, Criteria2:="<>BATCH", _
    Operator:=xlAnd'
    'ActiveSheet.Range("$A$1:$AA$45415").AutoFilter Field:=1, Criteria3:="<>Total*", _
    Operator:=xlAnd'
    Range("A2:AA2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("<1> Accurate Activity").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("A2").Select
    Sheets("Instructions").Select
    Range("A9").Select
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Autofilter, criteria from InputBox and Wildcard
    By gvaltat in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-18-2014, 01:33 PM
  2. [SOLVED] Macro Remove Unwanted Data
    By timbo1957 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-14-2013, 06:01 AM
  3. Concatinate and remove duplicates using vba macro using autofilter criteria
    By chakravarthysm in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-06-2013, 03:30 PM
  4. [SOLVED] MACRO to remove unwanted character.
    By visha_1984 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-07-2013, 03:47 AM
  5. Array formula to remove blanks (like autofilter)
    By CST in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-13-2012, 12:54 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1