Superfilter in VBA

The standard AutoFilter for selecting from lists is certainly a familiar and reliable thing. But to create complex conditions, you have to perform quite a few actions. For example, to filter values ​​falling within the range from 100 to 200, you need to expand the AutoFilter list with the mouse, select the option Condition (Custom), and in newer versions of Excel: Numeric Filters – Custom Filter (Number filters — Custom filter). Then, in the dialog box, specify two comparison operators, values, and a logical connection (AND-OR) between them:

Superfilter in VBA

Not too long, some will say. Yes, but if you have to repeat this procedure several dozen times a day? There is a way out – an alternative filter using a macro that will take the values ​​​​of the selection criteria directly from the cells of the sheet, where we simply enter them from the keyboard. In essence, it will be similar to an advanced filter, but working in real time. To implement such a thing, we need to take only two steps:

Step 1. Named Range for Conditions

First you need to create a named range where we will enter the conditions, and where the macro will take them from. To do this, you can insert a couple of empty lines right above the table, then select the cells for future criteria (in the figure it is A2: F2) and give them a name Termsby typing it in the name field in the upper left corner and pressing the Enter key. For clarity, I highlighted these cells in yellow:

Step 2. Adding a filtering macro

Now you need to add a filter macro to the current sheet by criteria from the created range Terms. To do this, right-click on the sheet tab and select the command Source text (Source text). In the Visual Basic editor window that opens, you need to copy and paste the text of the following macro:

Private Sub Worksheet_Change(ByVal Target As Range) Dim FilterCol As Integer Dim FilterRange As Range Dim CondtitionString As Variant Dim Condition1 As String, Condition2 As String If Intersect(Target, Range("Conditions")) Is Nothing Then Exit Sub On Error Resume Next Application.ScreenUpdating = False 'Define list data range Set FilterRange = Target.Parent.AutoFilter.Range 'Read conditions from all updated condition range cells For Each cell In Target.Cells FilterCol = cell.Column - FilterRange.Columns(1).Column + 1 If IsEmpty(cell) Then Target.Parent.Range(FilterRange.Address).AutoFilter Field:=FilterCol Else If InStr(1, UCase(cell.Value), " OR ") > 0 Then LogicOperator = xlOr ConditionArray = Split (UCase(cell.Value), " OR ") Else If InStr(1, UCase(cell.Value), " AND ") > 0 Then LogicOperator = xlAnd ConditionArray = Split(UCase(cell.Value), " AND ") Else ConditionArray = Array(cell.Text) End If End If 'form the first condition If Left(ConditionArray(0), 1) = "<" Or Left( ConditionArray(0), 1) = ">" Then Condition1 = ConditionArray(0) Else Condition1 = "=" & ConditionArray(0) End If 'form the second condition - if it exists If UBound(ConditionArray) = 1 Then If Left( ConditionArray(1), 1) = "<" Or Left(ConditionArray(1), 1) = ">" Then Condition2 = ConditionArray(1) Else Condition2 = "=" & ConditionArray(1) End If End If 'Enable filtering If UBound(ConditionArray) = 0 Then Target.Parent.Range(FilterRange.Address).AutoFilter Field:=FilterCol, Criteria1:=Condition1 Else Target.Parent.Range(FilterRange.Address).AutoFilter Field:=FilterCol, Criteria1:= Condition1, _ Operator:=LogicOperator, Criteria2:=Condition2 End If End If Next cell Set FilterRange = Nothing Application.ScreenUpdating = True End Sub

Все.

Now, when entering any conditions in the yellow cells of our named range, filtering will immediately work, displaying only the lines we need and hiding the unnecessary ones:

As in the case of classical Autofilter (Filter) и advanced filter (Advanced Filter), in our macro filter, you can safely use wildcards:

  • * (asterisk) – replaces any number of any characters
  • ? (question mark) – replaces any single character

and logical connective operators:

  • И – fulfillment of both conditions
  • OR – fulfillment of at least one of the two conditions

and any mathematical inequality symbols (>,<,=,>=,<=,<>).

When deleting the contents of the yellow range cells Terms filtering is automatically removed from the corresponding columns.

PS

  • If you have Excel 2007 or 2010 don’t forget to save the macro-enabled file (in xlsm format), otherwise the added macro will die.
  • This macro does not work with “smart tables”

  • What are macros, where to insert macro code in VBA, how to use them?
  • Smart Spreadsheets Excel 2007/2010
  • Advanced filter and some magic

 

Leave a Reply