VBA: Applying Autofilter to range including pivot

JustLukas

New member
Joined
Jun 10, 2014
Messages
10
Reaction score
0
Points
0
Hello!

I am trying to apply autofilter to range which includes a pivot, but i keep getting an "Autofilter method or Range class failed" error.


Lets say the range is A1:J100. The pivot is in between A1:G100. When manually applying the filter I just select the full range, press Ctrl+shift+L and it applies an autofilter even though there is a pivot. However when I do it with VBA, it doesnt allow it. Could anybody help please?

Code:
[COLOR=#00008B]
With[/COLOR] wb   

[COLOR=#00008B]Set[/COLOR] chk1AnalysRng = .Range([COLOR=#800000]"A1"[/COLOR], .cells(.Range([COLOR=#800000]"A1"[/COLOR]).[COLOR=#00008B]End[/COLOR](xlDown).Row,.Range([COLOR=#800000]"A1"[/COLOR]).[COLOR=#00008B]End[/COLOR](xlToRight).Column))

[COLOR=#00008B]End[/COLOR] [COLOR=#00008B]With

[/COLOR][COLOR=#00008B]With[/COLOR] chk1AnalysRng 

   .AutoFilter [COLOR=#808080]'<--- Error[/COLOR] 
   .AutoFilter field:=[COLOR=#800000]3[/COLOR], Criteria1:=Key 
   .AutoFilter field:=[COLOR=#800000]8[/COLOR], Criteria1:=[COLOR=#800000]"<>"

[/COLOR][COLOR=#00008B]End[/COLOR] [COLOR=#00008B]With

[/COLOR]
 

JustLukas

New member
Joined
Jun 10, 2014
Messages
10
Reaction score
0
Points
0
Managed to solve it by selecting the only the last cell in the header row and applying the filter to it.
 
Top