2

I am presently working with data from a mainframe, not unlike the data presented here. In this case, all I have are strings akin to 100-AAA for my criteria and for my target data set. I have searched for a similar solution, but I have not found a direct filter by range question for VBA on SO.

I am attempting to filter a large dataset based on a range of criteria. Originally, I started playing with collections in separate classes/subroutines, but then I compressed to the use of ranges within a single subroutine, as follows:

Sub FilterTest1()

Dim RngOne As Range
Dim LastCell As Long

Sheets("Criteria").Activate
Sheets("Criteria").Select
Range("A1").Select

LastCell = Application.WorksheetFunction.CountA(ActiveSheet.Range("A:A"))

Set RngOne = ActiveSheet.Range("A2:A" & LastCell)

Sheets("Sheet 1").Activate
Sheets("Sheet 1").Select

With ActiveSheet
    If .FilterMode Then
    .ShowAllData
    End If
End With

Columns("A:A").Select
Selection.AutoFilter
ActiveSheet.Range("A:A").AutoFilter Field:=1, Criteria1:=RngOne, Operator:=xlOr

End Sub

Unfortunately, my range of cells does not filter the data, even when using criteria such as Operator:=xlOr - it just displays the very last cell value used for the criteria. The VBA code does not fail - it just does not filter properly based on the range.

I feel certain that there exists a simple solution, but I cannot quite see it.

Community
  • 1
  • 1
Scott Conover
  • 1,421
  • 1
  • 14
  • 27
  • Did you consider using ADO so that you can use SQL commands to do the filtering (but still using Excel)? Performing an inner join between your criteria and the data set might fit the bill – barrowc Aug 08 '12 at 23:05
  • Does this earlier answer to a similar question help - http://stackoverflow.com/a/7863452/53614 ? I use ADO on machines at work where I don't have admin rights and it works OK. YMMV but it's worth giving it a shot – barrowc Aug 10 '12 at 02:28
  • Alright; I will give it a shot over the weekend and see how it deploys early next week. It may be well worth it to use ADO for some of my larger datasets, as anything relying on SQL is likely to execute that much faster in the long run. If it functions, well that means another +1 for your answer =) – Scott Conover Aug 10 '12 at 14:04

2 Answers2

9

Your current line:

ActiveSheet.Range("A:A").AutoFilter Field:=1, Criteria1:=RngOne, Operator:=xlOr

is trying to filter your first field by a Range object, not by each value in the range.

I've provided one way to do this below, by loading your values into an array and then passing the array to the filter criteria.

A few points worth noting to help you (and anyone else in the future):

  • I learned of the correct syntax to filter on a selection of objects by using the macro recorder.
  • This site helped me learn about the array trick: Pass Array to Filter Criteria
  • I also cleaned up your code to make it more efficient and faster, by getting rid of useless select statements and such. (Not a big deal for this little bit of code, but getting out of that practice will help you for bigger projects!)

Code Below: 

Sub FilterTest1()

Dim RngOne As Range, cell As Range
Dim LastCell As Long
Dim arrList() As String, lngCnt As Long

With Sheets("Criteria")
    LastCell = .Range("A" & Sheets("Criteria").Rows.Count).End(xlUp).Row
    Set RngOne = .Range("A2:A" & LastCell)
End With

'load values into an array
lngCnt = 0
For Each cell In RngOne
    ReDim Preserve arrList(lngCnt)
    arrList(lngCnt) = cell.Text
    lngCnt = lngCnt + 1
Next


With Sheets("Sheet 1")

    If .FilterMode Then .ShowAllData

    .Range("A:A").AutoFilter Field:=1, Criteria1:=arrList, Operator:=xlFilterValues

End With

End Sub
Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
  • Hello Scott, I think I see where you are going with this - use an array of strings as the criteria rather than a range. As for the code cleaning - much thanks, I regularly go through 10,000 rows at a time. However, when I tested your code, I ran into a "type mismatch" error message. After stepping into the code and checking the rest of the code syntax, it occurs on line 25: `.Range("A:A").AutoFilter Field:=1, Criteria1:=Array(strList), Operator:=xlFilterValues` . Do you do know why this may be occuring? The rest of the code appears to function correctly. – Scott Conover Aug 08 '12 at 21:47
  • Tested it - an exact match for the criteria. It looks like an array gets the job done very well. I had by and large switched to the framework of collections, dictionaries and hashsets as much as possible in C# for sorting purposes, but the operations you can do with arrays seem to work better for VBA. Deep down, I look forward to a strong, generic OO solution, and will likely post a Q towards that end, maybe on codereview.stackexchange. That said, your answer is excellent, and it gets the job done much faster than the recursive loop I used which crashed my excel sheet ;) – Scott Conover Aug 09 '12 at 14:18
3

A thing to note on is that the Criteria1 requires string variables as the argument. You can assign a range to an array then pass the string array to the criteria. Another note is that to pass an array to the criteria, to my knowledge, it has to be in a row, not a column eg. StringArray(0,3) instead of StringArray(3,0). Hence why you were getting the last value in your range. Just use Application.Transpose(StringArray) to convert to a row.

This little bit of code works for me, and is easily expanded to bigger arrays.

Sub test()
Dim StringArray(0, 1 To 3) As String 'This is a fixed dimension of an array, you can easily change to dynamic by using the ReDim statement.
ActiveSheet.AutoFilterMode = False 'To reset autofilter

Range("D3:D5").Select

For c = 1 To 3 'Step through the range that you wish to use as the criteria.
StringArray(0, c) = CStr(Selection(c, 1).Value)
Next

Range("A:A").AutoFilter Field:=1, Criteria1:=StringArray, Operator:=xlFilterValues

End Sub
Rory
  • 396
  • 1
  • 5