CODEWITHSUNDEEP
vbaexcel
Monic
Monic

Reputation: 746

VBA macro - AdvancedFilter method of Range class failed

I have such code in VBA Excel:

Sub MySub()
Dim sheet

Application.EnableEvents = False
Application.ScreenUpdating = False

'get current sheet name
sheet = ActiveSheet.Name                            

'I have criteria range in hidden sheet
Set cRng = Sheets("Hide_sheet.").Range("A14:O15")   
With Sheets("MySheet").Range("A44:O144")
    .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= cRng, _
     Unique:=False      'in these 2 lines is this error
End With

Application.EnableEvents = True
Application.ScreenUpdating = True

Sheets(sheet).Select 'return to the sheet where I was

End Sub

And I'm getting such error:
Run-time error '1004':
AdvancedFilter method of Range class failed

What I want to do with this macro?
I want to hide all rows, where are zero values in first column. In criteria range I have '<>0 in the appropriate column.

I use also pivot table - I have once 1 row and another time over 100. So that's why I use another sheet to show these rows. In my table I have all 100 rows fulled with formulas that refer to 100 rows in hidden sheet. If some row in Hide_sheet is empty, in MySheet appopriate row has zero value. So I always have 100 rows with values, only that these values are changing.

The macro starts when I change filter in pivot table:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Call MySub

End Sub

I've been seraching in the Internet a lot, but I haven't found solution for my problem yet. Maybe you will help me.

===
New informations:
Sometimes this macro works, sometimes not... It works when I got this error and I click debug macro, then stop debugging, change filter in pivot table and then I can run macro without any errors.

Upvotes: 2

Views: 3734

Answers (1)

Kamil Kaleciński
Kamil Kaleciński

Reputation: 31

I was looking for the solution for similar problem as well, and found that advanced filter code is not working if you run code with active cell selected inside Pivot Table area. Simply add some line before to select any cell outside pivot before advancedFilter command, and it should work

Upvotes: 3

Related Questions