CODEWITHSUNDEEP
excelpivotvba
Kippa2005
Kippa2005

Reputation: 41

How can I filter pivot table position in VBA?

I've tried finding an answer to this on this site and I'm having no luck so hopefully someone can help me. I can't provide an example sheet as the data is private, but I will attach a screen shot.

This is the graph and pivot table

I am wanting to try and remove the bottom two lines (Blank and the >15/5/18). The 15/5/18 date changes daily, so I can't reference it by name, and if I try to hide the one called blank, I get a 1004 error.

Sub removeblanks()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Application.ScreenUpdating = False

Set pt = ActiveSheet.PivotTables("PivotTable1")
Set pf = pt.PivotFields("Due Date")

For Each pi In pf.PivotItems
    pi.Visible = False 'Hide each by default, then show only the ones we want
    If pi.Value = "Jan" Then pi.Visible = True
    If pi.Value = "Feb" Then pi.Visible = True
    If pi.Value = "Mar" Then pi.Visible = True
    If pi.Value = "Apr" Then pi.Visible = True
    If pi.Value = "May" Then pi.Visible = True
    If pi.Value = "Jun" Then pi.Visible = True
    If pi.Value = "Jul" Then pi.Visible = True
    If pi.Value = "Aug" Then pi.Visible = True
    If pi.Value = "Sep" Then pi.Visible = True
    If pi.Value = "Oct" Then pi.Visible = True
    If pi.Value = "Nov" Then pi.Visible = True
    If pi.Value = "Dec" Then pi.Visible = True
Next

Application.ScreenUpdating = True   end sub

I tried other ways but none of them worked, and this was giving me the most success as I could hide or show the month fields, but once I try to hide the others, it errors.

In my picture I have 0 values, and in my data I have months with no data. These need to appear in this chart which is why it shows zero values, so I can't hide lines with no data.

There is a sort going on on VBA which puts everything in order, so the two lines I want gone will always be lines 13 & 14.

Any help is greatly appreciated, thanks

Upvotes: 0

Views: 175

Answers (2)

Axel Richter
Axel Richter

Reputation: 61870

Are the due dates from multiple years? If so, your pivoting is wrong since you are showing only the months and Feb in year 1 will be added to Feb in year 2 and Feb in year 3 ...

But nevertheless, in a date column of a pivot table are blanks lower than the minimal given date = blank = 0 = 1900-01-00. And then there is an item greater than the maximal given date. Both the dates are variable. But the following should work:

Set pt = ActiveSheet.PivotTables("PivotTable1")
Set pf = pt.PivotFields("Due Date")

For Each pit In pf.PivotItems
 pit.Visible = True
 If Left(pit.Name, 1) = "<" Then 'lower than the minimal given date = blank = 0 = 1900-01-00
  pit.Visible = False '
 End If
 If Left(pit.Name, 1) = ">" Then 'greater than the maximal given date
  pit.Visible = False '
 End If
Next

Upvotes: 1

Pavel_V
Pavel_V

Reputation: 1230

If you are absolutely sure that it will always be lines 13&14 did you try something like this?

Pi.Visible = True
If Pi.Position > 12 Then
 Pi.Visible = False
End If

inside your for loop.

Upvotes: 0

Related Questions