+ Reply to Thread
Results 1 to 5 of 5

AutoFilter during macro issue

Hybrid View

  1. #1
    Registered User
    Join Date
    01-29-2015
    Location
    Austin
    MS-Off Ver
    2013
    Posts
    12

    AutoFilter during macro issue

    I am using Microsoft Excel 2010. I have a macro that is not working they way I need it to work. I will try to explain it as best as I can. Any help will be appreciated.

    I have three columns with a couple thousand rows. I would like an autofilter to exclude(filter out) "0" and "(Blanks)" of the first column. I have recorded this event in the macro recorder and it works, but it is working backwards.

     Range("A1:C1").Select
        Selection.AutoFilter
    ActiveSheet.Range("$A$1:$C$2495").AutoFilter Field:=1, Criteria1:=Array("1" _
            , "10", "101", "102", "103", "1033", "104", "105", "106", "107", "108", "10816", "109", "11" _
            , "110", "111", "112", "1123", "113", "114", "115", "116", "117", "1170", "118", "119", "12" _
            , "120", "121", "122", "1227", "123", "124", "125", "126", "1267", "127", "128", "129", "13" _
            , "130", "131", "132", "133", "134", "135", "136", "137", "138", "139", "14", "140", "141", _
            "142", "143", "144", "145", "146", "147", "149", "15", "150", "151", "152", "153", "154", _
            "155", "156", "1560", "157", "158", "159", "16", "160", "1605", "161", "163", "164", "166", _
            "168", "169", "17", "170", "171", "172", "1725", "173", "174", "175", "176", "177", "178", _
            "179", "18", "180", "182", "183", "184", "185", "18540", "186", "187", "188", "189", "19", _
            "190", "191", "192", "194", "195", "196", "197", "198", "2", "20", "200", "20049", "201", _
            "203", "204", "205", "206", "207", "21", "210", "211", "212", "213", "214", "215", "216", _
            "218", "22", "221", "2217", "222", "223", "225", "226", "227", "228", "229", "23", "230", _
            "231", "232", "234", "237", "239", "24", "240", "241", "245", "246", "25", "250", "253", _
            "2533", "256", "2563", "257", "2572", "26", "261", "265", "27", "276", "278", "28", "280", _
            "281", "284", "287", "29", "290", "29011", "291", "294", "3", "30", "303", "3035", "304", _
            "306", "307", "31", "3191", "32", "320", "322", "33", "330", "332", "34", "340", "35", "352" _
            , "36", "37", "372", "376", "378", "38", "385", "39", "390", "392", "4", "40", "41", "417", _
            "42", "425", "43", "44", "442", "45", "456", "46", "464", "47", "48", "482", "49", "5", "50", _
            "51", "52", "53", "5398", "544", "55", "56", "57", "58", "59", "6", "60", "61", "62", "63", _
            "64", "65", "66", "67", "68", "69", "7", "70", "71", "73", "74", "748", "75", "76", "77", _
            "774", "78", "79", "8", "80", "81", "82", "83", "84", "85", "86", "87", "88", "89", "9", "90", _
            "91", "92", "93", "94", "95", "96", "97", "98", "980"), Operator:=xlFilterValues
    Instead of filtering out "0" and "(Blanks)", it is filtering in all others. This will be problematic because each time I run this event, the first column will contain different figures. What can I write that will filter out options instead of filtering in the options in my example?

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: AutoFilter during macro issue

    Try this.
    
    ActiveSheet.Range("$A$1:$C$2495").AutoFilter Field:=1, Criteria1:="<>", _
            Operator:=xlAnd, Criteria2:="<>0"
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    01-29-2015
    Location
    Austin
    MS-Off Ver
    2013
    Posts
    12

    Re: AutoFilter during macro issue

    Norie, thanks for the reply. Is this going to filter out "<>" and "<>0"? It looks to me like it is filtering them in and everything else out. I want the opposite of that.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: AutoFilter during macro issue

    Did you try the code?

    It filters for everything not equal (<>) to a blank cell or 0.

  5. #5
    Registered User
    Join Date
    01-29-2015
    Location
    Austin
    MS-Off Ver
    2013
    Posts
    12

    Re: AutoFilter during macro issue

    Yes, it works like you said. Thank you.

+ 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. autofilter issue
    By displaced in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-01-2016, 06:35 AM
  2. Issue with autofilter
    By kokapelly in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-05-2014, 03:14 PM
  3. Autofilter issue using macro when locking worksheets
    By rajiraji in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-18-2013, 11:27 PM
  4. [SOLVED] Copy annd paste macro: issue with sorting via autofilter
    By Smeddlesboy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-10-2013, 02:27 AM
  5. VBA AutoFilter issue
    By rowee21 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-11-2012, 10:29 AM
  6. AutoFilter Issue
    By ilee10 in forum Excel General
    Replies: 3
    Last Post: 11-28-2008, 06:28 AM
  7. AutoFilter Issue
    By Kumarrrr in forum Excel General
    Replies: 1
    Last Post: 04-16-2008, 11:16 AM

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