AutoFilter VBA

[brans1982]

Hi,

I have some code doing an autofilter for me...

Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=Array(View.Range("B6"), View.Range("B7"), View.Range("B8"), View.Range("B9"), Operator:=xlFilterValues

Works fine, but, sometimes not all the criteria cells will be populated and then I get an error message as the filter is looking for blanks.

Any way around this?

Thanks.

 

[Domenic]

Try...

<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> test()<br><br>    <SPAN style="color:#00007F">Dim</SPAN> MyArray() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> CritRng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> Cell <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> Cnt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> CritRng = View.Range("B6:B9")<br>    <br>    Cnt = 0<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cell <SPAN style="color:#00007F">In</SPAN> CritRng<br>        <SPAN style="color:#00007F">If</SPAN> Cell.Value <> "" <SPAN style="color:#00007F">Then</SPAN><br>            Cnt = Cnt + 1<br>            <SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> MyArray(1 <SPAN style="color:#00007F">To</SPAN> Cnt)<br>            MyArray(Cnt) = Cell.Value<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> Cell<br>        <br>    Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=MyArray, Operator:=xlFilterValues<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[brans1982]

Hi Domenic,

That worked perfectly.

Any chance you could explain it for me?

Many Thanks,

Conor.

 

[Domenic]

Have a look at the following code for which I've added comments. Basically, an array -- MyArray -- is created in which the criteria values are stored. The array is then used for the criteria when filtering the data.

Note, however, since we don't know how many values we have for the criteria, a dynamic array is declared instead of a fixed array. A dynamic array is declared with an empty set of parentheses -- MyArray(). Then, each time a value needs to be stored in the array, 'ReDim Preserve' is used. ReDim is used to change the array's size, and Preserve is used to preserve the existing values.

<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> test()<br><br>    <SPAN style="color:#007F00">'declare the variables</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> MyArray() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> CritRng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> Cell <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> Cnt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#007F00">'define the criteria range</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> CritRng = View.Range("B6:B9")<br>    <br>    <SPAN style="color:#007F00">'loop through each cell in the criteria range</SPAN><br>    Cnt = 0<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cell <SPAN style="color:#00007F">In</SPAN> CritRng<br>        <SPAN style="color:#007F00">'if the current cell is not empty, add its value to the array</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Cell.Value <> "" <SPAN style="color:#00007F">Then</SPAN><br>            Cnt = Cnt + 1<br>            <SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> MyArray(1 <SPAN style="color:#00007F">To</SPAN> Cnt)<br>            MyArray(Cnt) = Cell.Value<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> Cell<br>        <br>    <SPAN style="color:#007F00">'filter the data</SPAN><br>    Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=MyArray, Operator:=xlFilterValues<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[brans1982]

Thanks Domenic, thats very helpful!