Multilevel Autofilter

[G2K]

Hi,

can anybody help me to apply multilevel filter in vba i am trying to apply 2 levels if autofilter in VBA, but it removes filter apply filter again on selected region

below is the code

Private Sub test()
Dim Sh As Worksheet
Dim Rng As Range
Dim Rnge As Range
Dim ws As Worksheet
Dim Cell As Range
Dim TheList As New Collection
Dim Slist As New Collection
Dim i As Integer
Dim Rcount As Long
Dim eSum As Long
Dim ShTarget As Worksheet
Set Sh = Worksheets("Data")
With Sh
Set Rng = .Range("G2:G" & .Range("G" & .Rows.Count).End(xlUp).Row)
End With

On Error Resume Next
For Each Cell In Rng
TheList.Add Cell.Value, CStr(Cell.Value)
Next Cell

With Sh
Set Rnge = .Range("H2:H" & .Range("H" & .Rows.Count).End(xlUp).Row)
End With

On Error Resume Next
For Each Cell In Rnge
Slist.Add Cell.Value, CStr(Cell.Value)
Next Cell

MsgBox Slist.Count
On Error GoTo 0
Set Rng = Sh.Range("A1").CurrentRegion
Set Rnge = Sh.Range("A1").CurrentRegion
For l = 1 To Slist.Count
For i = 1 To TheList.Count
Rnge.AutoFilter
Rnge.AutoFilter field:=8, Criteria1:=Slist(l)
MsgBox Slist(l)
Rng.AutoFilter
Rng.AutoFilter field:=7, Criteria1:=TheList(i)
Rcount = Rng.SpecialCells(xlCellTypeVisible).Count

Set ShTarget = Works


MsgBox TheList(i) & "-" & Rcount

'Rng.SpecialCells(xlCellTypeVisible).Copy ShTarget.Range("A1")
Next i
Next l
With Sh
.AutoFilterMode = False
.Select
End With
End Sub

 

[Andrew Poulsom]

Try removing the second:

Rng.AutoFilter

That clears the filter if there is one.

 

[G2K]

Thanks Andrew,

got the point just after posting the thread, would appriciate if you help me to get the sum of the data in next column on the same cretaria

lets Column c is filtered on certain criteria and i want to add visible rows in column D.


Thanks

 

[Richard Schollar]

Look at using the Subtotal worksheet function with the first argument type of 9.