Missing date in range

limar9

New Member
Joined
Jan 26, 2018
Messages
1
I am automating a process that look at dates in a pivot table and make visible only the dates in the range. the code look for the last day that the program was run and make the range between that date and yesterday. The problem I am having is that if one of the dates in the range is missing from the dataset, the script stop and give me a "Unable to set the visible property of the PivotItem class" for the missing date. This is what I have so far
Sub test2()
'
' test2 Macro
'
Application.DisplayAlerts = False
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim sh As Worksheet
Dim Dt1 As Date
Dim lastDt As Date
Set sh = Sheets("Notes")
Set ws = Sheets("Auth Vol Sum")
Set pt = ws.PivotTables("PivotTable3")
Set pf = pt.PivotFields("Liab_dt")
Dt1 = Date - 1
lastDt = sh.Range("A9").Value
With pf
.ClearAllFilters
For Each pi In .PivotItems
If pi.Value = "(blank)" Then
pi.Visible = False
Else
If pi.Value = "" Then
Else
If pi.Value >= lastDt And pi.Value <= Dt1 Then
pi.Visible = True
Else
pi.Visible = False
End If
End If
End If
Next
End With
End Sub
Any ideas will be most appreciated.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Forum statistics

Threads
1,215,734
Messages
6,126,545
Members
449,317
Latest member
chingiloum

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top