Vba to filter by data depending on my critiria

[pedie]

Hi, I need help with Vba to filter by data depending on my critiria...

my critiria can change and are from A1:A to lastrow...
So is it possible to loop through & store my critia in array and then apply filter with it?

[/FONT]
[FONT=Courier New]Option Explicit
Sub test()
Range("A7").Select
    Selection.CurrentRegion.Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$6:$D$11").AutoFilter Field:=3, Criteria1:=Array( _
        [B][COLOR=blue]"DEPT1", "DEPT2", "DEPT3", "DEPT4"),[/COLOR][/B] Operator:=xlFilterValues
End Sub

 

[pedie]

It is returning runtime error 13, type mismatch....can someone please advice....
I dont even know how array thing really does work anyways....

[FONT=Courier New]Option Explicit[/FONT]
[FONT=Courier New]Sub FilterfromArray()[/FONT]
[FONT=Courier New]Dim MyArray(), rCnt As Long, cCnt As Integer[/FONT]
[FONT=Courier New]With Sheet3.Range("A1:A4")[/FONT]
[FONT=Courier New]   ReDim MyArray(1 To .Rows.Count, 1 To .Columns.Count)[/FONT]
[FONT=Courier New]   MyArray = Sheet3.Range("A1:C5")[/FONT]
[FONT=Courier New]End With[/FONT]
[FONT=Courier New]   Range("B1:J6").Select[/FONT]
[FONT=Courier New]   Selection.AutoFilter[/FONT]
[FONT=Courier New]   ActiveSheet.Range("$B$1:$J$6").AutoFilter Field:=4, Criteria1:=Array(MyArray), Operator:=xlFilterValues[/FONT]
[FONT=Courier New]End Sub[/FONT]

 

[toe222]

I started this a while back but ended up not needing it so never finished it, but maybe it will help you.

Sub Test()

Dim x, y, z As Integer

x = Cells(Rows.Count, 1).End(xlUp).Row

'Select range to filter and get unique values from range
Columns("A:A").Select
Selection.AdvancedFilter Action:=xlFilterInPlace, Unique:=True

'Create an array of unique values of from filtered range
ReDim MyArray(x) As String
y = 0
For Each cell In Range("A2:A" & x)
If cell.EntireRow.Hidden = False Then
MyArray = cell.Value
y = y + 1
End If
Next cell

'Remove filter
ActiveSheet.ShowAllData

'Filter range using each unique value from array
z = 0
Do Until z = y
Columns("A:A").Select
Selection.AutoFilter
ActiveSheet.Range("$C$1:$C$" & x).AutoFilter Field:=1, Criteria1:=MyArray(z)
Selection.AutoFilter
z = z + 1
Loop

End Sub

 

[pedie]

Hi thanks alot for sharing your code....
I tried it...however can't make it work with my situation....
'm not sure how this work...
Could you please explain a little bit on how this works whenever you have time...

Thanks again...

I started this a while back but ended up not needing it so never finished it, but maybe it will help you.

[/FONT]
[FONT=Courier New][FONT=Courier New]Sub Test()[/FONT]
[FONT=Courier New] [/FONT]
[FONT=Courier New]Dim x, y, z As Integer[/FONT]
[FONT=Courier New] [/FONT]
[FONT=Courier New]x = Cells(Rows.Count, 1).End(xlUp).Row[/FONT]
[FONT=Courier New] [/FONT]
[FONT=Courier New]'Select range to filter and get unique values from range[/FONT]
[FONT=Courier New]Columns("A:A").Select[/FONT]
[FONT=Courier New]Selection.AdvancedFilter Action:=xlFilterInPlace, Unique:=True[/FONT]
[FONT=Courier New] [/FONT]
[FONT=Courier New]'Create an array of unique values of from filtered range[/FONT]
[FONT=Courier New]ReDim MyArray(x) As String[/FONT]
[FONT=Courier New]y = 0[/FONT]
[FONT=Courier New]For Each cell In Range("A2:A" & x)[/FONT]
[FONT=Courier New]If cell.EntireRow.Hidden = False Then[/FONT]
[FONT=Courier New]MyArray(y) = cell.Value[/FONT]
[FONT=Courier New]y = y + 1[/FONT]
[FONT=Courier New]End If[/FONT]
[FONT=Courier New]Next cell[/FONT]
[FONT=Courier New] [/FONT]
[FONT=Courier New]'Remove filter[/FONT]
[FONT=Courier New]ActiveSheet.ShowAllData[/FONT]
[FONT=Courier New] [/FONT]
[FONT=Courier New]'Filter range using each unique value from array[/FONT]
[FONT=Courier New]z = 0[/FONT]
[FONT=Courier New]Do Until z = y[/FONT]
[FONT=Courier New]Columns("A:A").Select[/FONT]
[FONT=Courier New]Selection.AutoFilter[/FONT]
[FONT=Courier New]ActiveSheet.Range("$C$1:$C$" & x).AutoFilter Field:=1, Criteria1:=MyArray(z)[/FONT]
[FONT=Courier New]Selection.AutoFilter[/FONT]
[FONT=Courier New]z = z + 1[/FONT]
[FONT=Courier New]Loop[/FONT]
[FONT=Courier New] [/FONT]
[FONT=Courier New]End Sub[/FONT]

 

[toe222]

It is filtering column A to get a unique set of data to store in an array to use as criteria to filter. It loops thru filtering 1 criteria at a time but it sounded like wanted to use all the data in the array to filter just once. I would think this could be done with some minor changes. I am not sure how much VB you know but if you step thru the code (f8) you will get the idea of what is doing. If not let me know and i will see if i can explain it further or actually modify it to fit your needs.

 

[pedie]

toe222, thanks again, appriciate your help!

 

[meldoc]

Hi Pedie

Try.

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> FilterfromArray()<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">With</SPAN> Sheet1<br>    MyArray = .Range("A1:A4")<br>    .Range("A7").AutoFilter Field:=3, Criteria1:=Application.Transpose(MyArray), _<br>        Operator:=xlFilterValues<br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[pedie]

Brian, exactly what I need & simplier to understand too....Thanks alot...!!!