{"id":3767,"date":"2021-11-18T12:15:33","date_gmt":"2021-11-18T20:15:33","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3767"},"modified":"2021-11-18T12:15:35","modified_gmt":"2021-11-18T20:15:35","slug":"how-to-filter-out-multiple-criteria-using-excel-vba","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3767","title":{"rendered":"How to Filter Out Multiple Criteria Using Excel VBA"},"content":{"rendered":"\n<p>Questions:<a href=\"https:\/\/stackoverflow.com\/posts\/28575754\/timeline\"><\/a><\/p>\n\n\n\n<p>I have 8 variables in columns A, 1,2,3,4,5, and A, B, C.<br>My aim is to filter out A, B, C and display only 1-5.<\/p>\n\n\n\n<p>I can do this using the following code:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>My_Range.AutoFilter Field:=1, Criteria1:=Array(\"1\", \"2\", \"3\",\"4\",\"5\"), _\n    Operator:=xlFilterValues\n<\/code><\/pre>\n\n\n\n<p>But what the code does is filters variables 1 to 5 and display them.<\/p>\n\n\n\n<p>I want to do the opposite but yield the same result, by filtering out A, B, C and showing variables 1 to 5. What is the solution?<\/p>\n\n\n\n<p>It is possible to filter values not equal to some criteria, but only up to two values that don&#8217;t work for this situation:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Range.AutoFilter Field:=1, Criteria1:=\"=&lt;>A\", Operator:=xlOr, Criteria2:=\"=&lt;>B\"\n\nThere are a couple of workarounds possible depending on the exact problem:\r\n\r\nUse a \"helper column\" with a formula in column B and then filter on that - e.g. =ISNUMBER(A2) or =NOT(A2=\"A\", A2=\"B\", A2=\"C\") then filter on TRUE\r\nIf you can't add a column, use autofilter with Criteria1:=\">-65535\" (or a suitable number lower than any you expect) which will filter out non-numeric values - assuming this is what you want\r\nWrite a VBA sub to hide rows (not exactly the same as an autofilter but it may suffice depending on your needs).\r\nFor example:\r\n\r\nPublic Sub hideABCRows(rangeToFilter As Range)\r\n  Dim oCurrentCell As Range\r\n  On Error GoTo errHandler\r\n\r\n  Application.ScreenUpdating = False\r\n  For Each oCurrentCell In rangeToFilter.Cells\r\n    If oCurrentCell.Value = \"A\" Or oCurrentCell.Value = \"B\" Or oCurrentCell.Value = \"C\" Then\r\n      oCurrentCell.EntireRow.Hidden = True\r\n    End If\r\n  Next oCurrentCell\r\n\r\n  Application.ScreenUpdating = True\r\n  Exit Sub\r\n\r\nerrHandler:\r\n    Application.ScreenUpdating = True\r\nEnd Sub\r\n\n\n\nRef: https:\/\/stackoverflow.com\/questions\/28575754\/filter-out-multiple-criteria-using-excel-vba\r\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Questions: I have 8 variables in columns A, 1,2,3,4,5, and A, B, C.My aim is to filter out A, B, C and display only 1-5. I can do this using the following code: But what the code does is filters variables 1 to 5 and display them. I want to do the opposite but yield <a class=\"read-more\" href=\"https:\/\/SUMMALAI.COM\/?p=3767\">Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,18],"tags":[945,944],"class_list":["post-3767","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-microsoft-office","tag-filter-with-multiple-criteria-using-excel-vba","tag-multiple-criterias-excel-vba"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3767","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3767"}],"version-history":[{"count":1,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3767\/revisions"}],"predecessor-version":[{"id":3768,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3767\/revisions\/3768"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3767"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3767"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3767"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}