filter out multiple criteria using excel vba

Question

I have 8 variables in column 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   My Range AutoFilter Field  1  Criteria1  Array  1    2    3   4   5          Operator  xlFilterValues   But what the code does is it filters variables 1 to 5 and displays them   I want to do the opposite  but yielding the same result  by filtering out A  B  C and showing variables 1 to 5  I tried this code   My Range AutoFilter Field  1  Criteria1  Array   lt  gt A     lt  gt B     lt  gt C          Operator  xlFilterValues   But it did not work   Why cant I use this code    It gives this error      Run time error 1004 autofilter method of range class failed   How can I perform this

User · Answer

Replace Operator  xlOr with Operator  xlAnd between your criteria  See below the amended script  myRange AutoFilter Field  1  Criteria1    lt  A   Operator  xlAnd  Criteria2    lt  B   Operator  xlAnd  Criteria3    lt  C

User · Answer

An option using AutoFilter    Option Explicit  Public Sub FilterOutMultiple       Dim ws As Worksheet  filterOut As Variant  toHide As Range      Set ws   ActiveSheet     If Application WorksheetFunction CountA ws Cells    0 Then Exit Sub  Empty sheet      filterOut   Split  A B C D E F G        Application ScreenUpdating   False     With ws UsedRange Columns  A           If ws FilterMode Then  AutoFilter         AutoFilter Field  1  Criteria1  filterOut  Operator  xlFilterValues         With  SpecialCells xlCellTypeVisible              If  CountLarge  gt  1 Then Set toHide    Cells  Remember unwanted  A  B  and C          End With         AutoFilter         If Not toHide Is Nothing Then             toHide Rows Hidden   True                    Hide unwanted  A  B  and C              Cells 1  Rows Hidden   False                 Unhide header         End If     End With     Application ScreenUpdating   True End Sub

User · Answer

Alternative using VBA s Filter function  As an innovative alternative to  schlebe  s recent answer  I tried to use the Filter function integrated in VBA  which allows to filter out a given search string setting the third argument to False  All  negative  search strings  e g  A  B  C  are defined in an array  I read the criteria in column A to a datafield array and basicly execute a subsequent filtering  A - C  to filter these items out    Code  Sub FilterOut   Dim ws  As Worksheet Dim rng As Range  i As Integer  n As Long  v As Variant   1  define strings to be filtered out in array   Dim a                        declare as array   a   Array  A    B    C        lt  lt  filter out values   2  define your sheetname and range  e g  criteria in column A    Set ws   ThisWorkbook Worksheets  FilterOut     n   ws Range  A   amp  ws Rows Count  End xlUp  row   Set rng   ws Range  A2 A   amp  n    3  hide complete range rows temporarily   rng EntireRow Hidden   True   4  set range to a variant 2-dim datafield array   v   rng   5  code array items by appending row numbers   For i   1 To UBound v   v i  1    v i  1   amp       amp  i   1  Next i   6  transform to 1-dim array and FILTER OUT the first search string  e g   A    v   Filter Application Transpose Application Index v  0  1    a 0   False  False    7  filter out each subsequent search string  i e   B  and  C    For i   1 To UBound a   v   Filter v  a i   False  False   Next i   8  get coded row numbers via split function and unhide valid rows   For i   LBound v  To UBound v        ws Range  A   amp  Split v i   amp            1   EntireRow Hidden   False   Next i End Sub

User · Answer

I think  from experimenting - MSDN is unhelpful here  that there is no direct way of doing this   Setting Criteria1 to an Array is equivalent to using the tick boxes in the dropdown - as you say it will only filter a list based on items that match one of those in the array   Interestingly  if you have the literal values   lt  gt A  and   lt  gt B  in the list and filter on these the macro recorder comes up with  Range AutoFilter Field  1  Criteria1     lt  gt A   Operator  xlOr  Criteria2     lt  gt B    which works   But if you then have the literal value   lt  gt C  as well and you filter for all three  using tick boxes  while recording a macro  the macro recorder replicates precisely your code which then fails with an error   I guess I d call that a bug - there are filters you can do using the UI which you can t do with VBA   Anyway  back to your problem   It is possible to filter values not equal to some criteria  but only up to two values which doesn t work for you   Range   A 1  A 9   AutoFilter Field  1  Criteria1    lt  gt A   Criteria2    lt  gt B   Operator  xlAnd   There are a couple of workarounds possible depending on the exact problem    Use 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 If you can t add a column  use autofilter with Criteria1    gt -65535   or a suitable number lower than any you expect  which will filter out non-numeric values - assuming this is what you want Write a VBA sub to hide rows  not exactly the same as an autofilter but it may suffice depending on your needs     For example   Public Sub hideABCRows rangeToFilter As Range    Dim oCurrentCell As Range   On Error GoTo errHandler    Application ScreenUpdating   False   For Each oCurrentCell In rangeToFilter Cells     If oCurrentCell Value    A  Or oCurrentCell Value    B  Or oCurrentCell Value    C  Then       oCurrentCell EntireRow Hidden   True     End If   Next oCurrentCell    Application ScreenUpdating   True   Exit Sub  errHandler      Application ScreenUpdating   True End Sub

User · Answer

Here an option using a list written on some range  populating an array that will be fiiltered  The information will be erased then the columns sorted   Sub Filter Out Values     Automation to remove some codes from the list Dim ws  ws1 As Worksheet Dim myArray   As Variant Dim x  lastrow As Long Dim cell As Range  Set ws   Worksheets  List   Set ws1   Worksheets 8  lastrow   ws Cells Application Rows Count  1  End xlUp  Row   Go through the list of codes to exclude For Each cell In ws Range  A2 A   amp  lastrow       If cell Offset 0  2  Value    X  Then  If the Code is associated with  X          ReDim Preserve myArray x   Initiate array         myArray x    CStr cell Value   Populate the array with the code         x   x   1  Increase array capacity         ReDim Preserve myArray x   Redim array     End If  Next cell  lastrow   ws1 Cells Application Rows Count  1  End xlUp  Row ws1 Range  C2 C   amp  lastrow  AutoFilter field  3  Criteria1  myArray  Operator  xlFilterValues ws1 Range  A2 Z   amp  lastrow  SpecialCells xlCellTypeVisible  ClearContents ws1 Range  A2 Z   amp  lastrow  AutoFilter field  3   Sort columns lastrow   ws1 Cells Application Rows Count  1  End xlUp  Row  Sort with 2 criteria With ws1 Range  A1 Z   amp  lastrow       Resize lastrow  Sort       key1  ws1 Columns  B    order1  xlAscending  DataOption1  xlSortNormal        key2  ws1 Columns  D    order1  xlAscending  DataOption1  xlSortNormal        Header  xlYes  MatchCase  False  Orientation  xlTopToBottom  SortMethod  xlPinYin End With  End Sub

User · Answer

This works for me  This is a criteria over two fields columns  9 and 10   this filters rows with values  0 on column 9 and rows with values 4  7  and 8 on column 10  lastrow is the number of rows on the data section   ActiveSheet Range   A 1  O    amp  lastrow  AutoFilter Field  9  Criteria1    gt 0   Operator  xlAnd ActiveSheet Range   A 1  O    amp  lastrow  AutoFilter Field  10  Criteria1  Arr  4   7   8    Operator  xlFilterValues

User · Answer

I don t have found any solution on Internet  so I have implemented one   The Autofilter code with criteria is then  iColNumber   1 Dim aFilterValueArray   As Variant Call ConstructFilterValueArray aFilterValueArray  iColNumber  Array  A    B    C     ActiveSheet range sRange  AutoFilter Field  iColNumber         Criteria1  aFilterValueArray         Operator  xlFilterValues   In fact  the ConstructFilterValueArray   method  not function  get all distinct values that it found in a specific column and remove all values present in last argument   The VBA code of this method is                                                                   ConstructFilterValueArray                                                                  Sub ConstructFilterValueArray a   As Variant  iCol As Integer  aRemoveArray As Variant       Dim aValue As New Collection     Call GetDistinctColumnValue aValue  iCol      Call RemoveValueList aValue  aRemoveArray      Call CollectionToArray a  aValue   End Sub                                                                   GetDistinctColumnValue                                                                  Sub GetDistinctColumnValue ByRef aValue As Collection  iCol As Integer       Dim sValue As String      iEmptyValueCount   0     iLastRow   ActiveSheet UsedRange Rows Count      Dim oSheet  Set oSheet   Sheets  X        Sheets  Data            range Cells 1  iCol   Cells iLastRow  iCol                  AdvancedFilter Action  xlFilterCopy                               CopyToRange  oSheet range  A1                                 Unique  True      iRow   2     Do While True         sValue   Trim oSheet Cells iRow  1           If sValue      Then             If iEmptyValueCount  gt  0 Then                 Exit Do             End If             iEmptyValueCount   iEmptyValueCount   1         End If          aValue Add sValue         iRow   iRow   1     Loop  End Sub                                                                   RemoveValueList                                                                  Sub RemoveValueList ByRef aValue As Collection  aRemoveArray As Variant       For i   LBound aRemoveArray  To UBound aRemoveArray          sValue   aRemoveArray i          iMax   aValue Count         For j   iMax To 0 Step -1             If aValue j    sValue Then                 aValue Remove  j                  Exit For             End If         Next j      Next i  End Sub                                                                   CollectionToArray                                                                  Sub CollectionToArray a   As Variant  c As Collection       iSize   c Count - 1     ReDim a iSize       For i   0 To iSize         a i    c Item i   1      Next  End Sub   This code can certainly be improved in returning an Array of String but working with Array in VBA is not easy   CAUTION  this code work only if you define a sheet named X because CopyToRange parameter used in AdvancedFilter   need an Excel Range    It s a shame that Microfsoft doesn t have implemented this solution in adding simply a new enum as xlNotFilterValues       or xlRegexMatch

[excel] filter out multiple criteria using excel vba

Examples related to excel

Examples related to vba

Examples related to excel-2010

Examples related to excel-2007