Hello!
I have a pivot table which I have tricked it to autofilter with VBA. The code that I have been using is the following:
Code errors out at:
c.AutoFilter Field:=c.Column - i, _
Visibledropdown:=False
Like I said, I have used this in the past with NO ISSUES. The only thing that has changed is the number of columns in the pivot table (a few columns were added). The pivot table column range is from Column E to Column AC. I changed the cells for which I want to hide the visible drop down but the code errors out. I dont know why the code errors out. Anyone have any ideas? Do I need to change something?
"Run-time error 1004: Autofilter method of Range class failed"
Sub WeekAutoFilter()
'hides arrows in specified range
Dim c As Range
Dim i As Integer
Dim rng As Range
Set rng = Range("D16:AD16")
With Worksheets("Analysis")
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
End If
rng.AutoFilter
End With
i = rng.Cells(1, 1).Column - 1
'****************************************************************************************
'******** Change Range to satisfy # of Columns in Pivot Table + 1 column on each side ***
'****************************************************************************************
rng.AutoFilter
For Each c In rng
Select Case c.Address
Case "$D$16", "$AD$16"
c.AutoFilter Field:=c.Column - i, _
Visibledropdown:=False
End Select
Next
'****************************************************************************************
'****************************************************************************************
'****************************************************************************************
Range("E16").Select
End Sub
Bookmarks