2 Advanced filters in same active workbook
2 advanced filter in same active workbook
WHERE ?
My data range is in Sheet.(Num)
My dynamic criteria is in Sheet.(criteria)
I do not need to copy my extract anywhere else , just need to count the special cells displayed.
WHAT is the PROCESS ?
There are two advanced filters in my code . Lets call them AdvancedFilter1 & AdvancedFilter2
My criteria is dynamic but with the same headings . So for both the advanced filter the headings of criteria are same , only the data below them changes.
When AdavncedFilter1 removes the extract , I do a counting process and then it goes to second AdvancedFilter2 & I do the counting again .
I do not get any error while running the code , my problem is when going to second advanced filter , the criteria selection from AdvancedFilter1 is carried to AdvancedFilter2 and the extract shown is of both the criteria.
E.g.
IDEAL PROCESS (Expected)
AdvancedFilter1 Extract1 (5) ShowallData AdvancedFilter2 Extract2 (20)
MY CODE RESULTS
AdvancedFilter1 Extract1 (5) ShowallData AdvancedFilter2 Extract2 (25)
MY QUERY
I need to know how to erase/delete the criteria-selection of advanced filter memory before next Advanced filter starts. Showalldata , only shows the data , but selection is not deleted.
Please refer to the code which I am using
CODE
Sub
Nextlevel3:
Sheets("Num").Select
r = Application.Match("System Tier", ActiveSheet.Range("A1:AZ1"), 0)
rw = Cells(Rows.Count, 1).End(xlUp).Row
clmn = Cells(1, Columns.Count).End(xlToLeft).Column
Set rngSp = ActiveSheet.Range(Cells(1, r), Cells(1, r).End(xlDown))
Range(Cells(1, 1), Cells(rw, clmn)).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Criteria").Range("B2").CurrentRegion, Unique:=False
cntsp = rngSp.SpecialCells(xlCellTypeVisible).Cells.Count
cntsp = cntsp - 1
If cntsp = 0 Then GoTo condition8
condition7:
COUNTING PROCESS 1
condition8:
Set rngSp = ActiveSheet.Range(Cells(1, r), Cells(1, r).End(xlDown))
rw = Cells(Rows.Count, 1).End(xlUp).Row
clmn = Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(1, 1), Cells(rw, clmn)).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Criteria").Range("G2").CurrentRegion, Unique:=False
cntsp1 = rngSp.SpecialCells(xlCellTypeVisible).Cells.Count
cntsp1 = cntsp1 - 1
If cntsp1 = 0 Then GoTo autofiltfalse Else
COUNTING PROCESS 2
autofiltfalse:
Sheets("Criteria").Select
Range("E3:E1000").clear
Range("I3:I1000").clear
Range("J3:J1000").clear
End Sub
Recent comments
5 years 42 weeks ago
6 years 28 weeks ago
6 years 40 weeks ago
6 years 42 weeks ago
6 years 43 weeks ago
6 years 49 weeks ago
7 years 5 weeks ago
7 years 5 weeks ago
7 years 5 weeks ago
7 years 5 weeks ago