3

Background

This could end up being a rather silly question, however I wish to still ask it to be sure. As of recently I been introduced to AdvancedFilter through VBA, and reading the CriteriaRange parameter takes a Variant data type I thought it may have been able to take an array instead of an actual Range as the name suggests.

Sample Data

For this I have set up a simple data set:

| Hdr1 | Hdr2 | Hdr3 |
|------|------|------|
| A    | X    | 1    |
| B    | Y    | 2    |
| A    | Z    | 3    |
| A    | Y    | 4    |
| B    | Y    | 5    |
| A    | Z    | 6    |

This data sits on sheet with CodeName "Source". Besides that, I added a sheet called "Destination" as to where I want the filtered criteria to be pasted through xlFilterCopy.

Code

A simple working code would be:

Sub FilterTest()

Dim rng as range
Dim arr(1 To 2, 1 To 3) As Variant

arr(1, 1) = "Hdr1"
arr(1, 2) = "Hdr2"
arr(1, 3) = "Hdr3"
arr(2, 1) = "A"
'arr(2, 2) = "" 'Leaving empty for now
'arr(2, 3) = "" 'Leaving empty for now

Set rng = Destination.Range("A1:C2")
rng = arr

Source.Range("A1:C7").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rng, CopyToRange:=Destination.Range("A4")

End Sub

Problem/Curiosity

As the above works fine, I'm curious if there is a way to avoid filling the CriteriaRange prior to applying the AdvancedFilter, or even implement the arr array directly. As is, I believe we can't but I am curious to be proven wrong.

Whereas filling the evenly dimensioned rng with arr works, obviously the following line will error (1004) out:

Source.Range("A1:D7").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=arr, CopyToRange:=Destination.Range("A4")

The Variant data type for CriteriaRange seems to be expected (as per the documentation) but seemingly only the Range objects are actual working parameters.

Any definate answer as to whether it's possible? =)

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 2
    The MS documentation is not helpful! Shows how to use the command in its blandest form, but doesn't provide any clues on why it works or the impact of some variations in usage have. But my main question is "Why?". Apart from curiosity or the love of a good coding challenge, of course. If you are using an array to determine the `Criteria`, then your code is probably faster if you worked through an array of source values as well - negating the need for expensive range conversions and application function calls. I know this comment is challenging the framing of the question - all in good fun. – AJD Sep 20 '19 at 21:53
  • @AJD, thanks for the response. As to the 'why?', mainly to see if there is a way to use the parameter without a physical representation of the CriteriaRange anywhere in the project. And yes, that's merely a question comming from curiosity =) – JvdV Sep 20 '19 at 22:00
  • Same question, more or less, was asked on https://stackoverflow.com/questions/34532282/can-advanced-filter-criteria-be-in-the-vba-rather-than-a-range and https://stackoverflow.com/questions/34614417/can-autofilter-take-both-inclusive-and-non-inclusive-wildcards-from-dictionary-k and it seems that nobody found the way. – David García Bodego Sep 21 '19 at 12:49
  • 1
    It's called CriteriaRange so a range only I would say, Variant because an empty range, String or Variant will also work on Advanced Filter. – EvR Sep 23 '19 at 14:24
  • I don't think CriteriaRange can be filled directly without range filter. – user11982798 Sep 25 '19 at 00:39
  • For your need, I think you can use normal filter. You can put your criteria directly or store in an array, then you can copy the result to your destination. If you want, I will make a sample with your simple data and your criteria. – user11982798 Sep 25 '19 at 01:39
  • @user11982798, thanks but no, working with `AdvancedFilter` per se is not the problem, hence why I provided a working example in my question. The problem/question I got is wheather we can manipulate the parameter to take a variant variable being something different than a range. To prevent physical representation of `CriteriaRange` within the project. – JvdV Sep 25 '19 at 09:17

0 Answers0