Filter Multiple Values at Once In Excel

VBA Series Article #1

We all work with spreadsheets one way or the other, I think it's a basic requirement when it comes to analyzing Data. And dealing with spreadsheets can become a frustration when there is no straight way to achieve a simple thing. In this Article that simple thing is Filtering data. Now everyone knows how to filter data on Spreadsheet....Right?

But what if I say that we have a 10k Rows of data and 45 columns. Shouldn't be a problem, shortcut to apply filter in Excel is (CTRL + Shift + L). Press the shortcut and it's done?

But what if we want to filter 30 values (String or Integer) from the 17th Column ? ??

No! Selecting the Drop Down and Choosing 30 Values is not going to be easy!???♂????♂?

There are many ways to do it, one being applying VlookUp or Using Advanced Filter. But this little piece of code below can save all that time spent in Choosing from Drop Down , Applying Vlookup or Fiddling with selection of Advanced Filter.

• Copy the Values to be Filtered
• Select the Header of the Column on which you want to apply the Filter.
• Click the Macro Shortcut.?

This can save a lot of time while working. All that needs to done is set up this code for the First time.

Sub multi_filter()

    Dim i As Integer
    Dim Test As String
    Dim clipboard As MSForms.DataObject
    
    Set clipboard = New MSForms.DataObject
    
    clipboard.GetFromClipboard
    Test = clipboard.GetText
    
    Test = Replace(Test, Chr(13), "|")
    Test = Trim(WorksheetFunction.Clean(Test))
    
    Dim ab() As String
    
    ab = Split(Test, "|")
    
    ReDim Preserve ab(UBound(ab) - 1)
    
    ActiveSheet.UsedRange.AutoFilter Field:=Selection.Column, Criteria1:=ab, Operator:= _
        xlFilterValues

End Sub

Technicality ??: First we are copying the Data, that saves it to the Clipboard. Then the Macro manipulates and forms an Array of values that needs to be Filtered. Lastly Macro applies the filter on the Selected Cell i.e the Column Header.

Setting up the Code:

• Activate the Personal Macro Workbook.

• Insert a New Module and copy paste the code in it.
• Customize the Ribbon and Add the Shortcut.

Ping me in case you are trying to implement this and are stuck somewhere. Follow to get updates on more useful tricks in future.??

#VBA #Excel #Macros #Automation