{"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

Questions:<\/a><\/p>\n\n\n\n

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.<\/p>\n\n\n\n

I can do this using the following code:<\/p>\n\n\n\n

My_Range.AutoFilter Field:=1, Criteria1:=Array(\"1\", \"2\", \"3\",\"4\",\"5\"), _\n    Operator:=xlFilterValues\n<\/code><\/pre>\n\n\n\n
But what the code does is filters variables 1 to 5 and display them.<\/p>\n\n\n\n
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
It is possible to filter values not equal to some criteria, but only up to two values that don’t work for this situation:<\/p>\n\n\n\n
Range.AutoFilter Field:=1, Criteria1:=\"=<>A\", Operator:=xlOr, Criteria2:=\"=<>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":"
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 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,"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":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t