Trying to Find Work-Around When Removing Pivot Table Data and There's No Data Left???

[pawest]

Hello,
I use code to gather data, create pivot tables, and assess the data. I exclude certain "Account" ("Account" is the column header) data criteria on certain pivot tables. For example, if any pivot table row field under "Account" contains "XXX", I remove XXX from the pivot table by filtering out XXX.

Frequently, XXX is the only "Account" in the data table, meaning there are no other accounts and the only relevant data falls under XXX. So, when the VBA code is ran, it executes but an error message pops up. I know this error is occurring because, on these certain occasions, I'm filtering out the only data in the table.

Since it's not possible to filter out all data within a pivot table, I tried hard coding zeros in the rows/columns of the data tables so the pivot tables could have a dummy value to reference. This works, but other formulas/functions/areas in my spreadsheet get messed up. I would like to find another way to handle the issues of filtering out the only data in a pivot table.

Please advise. Thanks!

 

[par60056]

Are you getting an error because you have every value for a field marked not visible?

Does your pivot table have page fields?

Could you have a blank row in the pivot table source, leave the field "(blank)" turned off in the page field but leave it turned on in the Account field?

Then once you have set the pivot items for the report, check

if (pivottable.RowRange.Count / pivottable.RowFields.Count > 2) then
     ' There is valid data in the pivot table to report
end if

 

[pawest]

Thanks for the response. I'm getting an error because I have every value for a field marked not visible. My pivot table does have a page field.

... I wish there was a nifty way to say if all data doesn't meet my criteria, and I set all fields equal to not visible, then default to a blank page field.

For me, it would make more sense to add "FAKEACCOUNT" instead of adding a blank field. Here's what I have so far and it's not working exactly how I want:

Sub InsertFakeAccounts()


Dim lastCol As Integer, _
    eachCol As Integer, _
    lastRow As Integer

Dim wSheet As Worksheet

    
' **************** Setting variables ****************
lastCol = Range("A1").End(xlToRight).Column
       
        
        '****************** Adding FakeAccounts Calculations ******************
        ' A catch-all to make sure there's at least some value on the sheet or else the pivot tables won't work
        For Each wSheet In Worksheets
    
            If wSheet.Name Like "yData*" Or _
                wSheet.Name Like "xData*" Or _
                wSheet.Name Like "aData*" Then
        
                    For eachCol = 1 To lastCol
                        If Cells(1, eachCol).Value = "Account" Or _
                            Cells(1, eachCol).Value = "Account*" Or _
                            Cells(1, eachCol).Value = "*Account" Or _
                            Cells(1, eachCol).Value = "*Account*" Then
                            
                                lastRow = Cells(Rows.count, eachCol).End(xlUp).Row
                                
                                Debug.Print lastRow
                                
                                Cells(lastRow + 1, eachCol).Select
                                Selection.Value = "FakeAccount"
                        
                        End If
                    Next eachCol
            End If
        Next wSheet

End Sub

The above code is not working properly, first of all. And I would rather find another way to handle this issue. Any feedback is appreciated.

 

[Domenic]

Which version of Excel are you using? If you're using Excel 2007 or later, try the following...

1) Prevent old items from being retained in Pivot Field dropdowns by changing the setting in PivotTable Options:

1. Right-click a cell in the pivot table
2. Click on PivotTable Options
3. Click on the Data tab
4. In the Retain Items section, select None from the drop down list.
5. Click OK
6. Refresh the pivot table

2) Then try something like this...

[FONT=Courier New][COLOR=darkblue]With[/COLOR] ActiveSheet.PivotTables("PivotTable1").PivotFields("Account")
    [COLOR=darkblue]If[/COLOR] .PivotItems.Count = 1 And .PivotItems(1).Name = "XXX" [COLOR=darkblue]Then[/COLOR]
        [COLOR=green]'XXX is the only available account[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]With[/COLOR][/FONT]

 

[par60056]

The pivot value "(blank)" is a system value for there was blank data in the source range. So you get that for free just by including an extra row in the source for the pivot table.

When you are looping through the pivot field to set the value visibility, set a boolean when you set one visible and the at the end of the loop if the boolean is true, set "(blank)" to not visible.

 

[par60056]

Thank you Domenic. I continue to find things that I thought were broken in 2000 and 2003 that are fixed in 2007 and 2010. I assume there is a way to set the "Retain Items" from VBA. I'll have to look. I always have the VBA start fresh rebuilding the pivot when the data is refreshed because of the issue of retained items.

I got the impression from pawest's initial question that there could be multiple accounts matching the criteria to turn them to not visible.

 

[pawest]

Hey everyone,

Thanks you for your responses. I'm going to go with selecting an extra blank row, using Domenic's suggestion to remove any retained items, and I'll just have to always show (blanks) in my tables.

I'll post my code when I get it working. Thanks!

 

[par60056]

Fell free to post it if you get it close but need a little more.

FYI, I just finished a project that has about 220K lines of data, generates 7 different pivot tables from it and loops through the page fields on each pivot copying the results to a report sheet to format for presentation.

The end result is something like 480 files, each with the 7 reports in it for a different set of page values.

No matter how complex you think your project either somebody will come up with a way to make it more complex or somebody else has one already.

Good luck.

 

[Domenic]

I assume there is a way to set the "Retain Items" from VBA.

For Excel 2002 and later...

[FONT=Courier New]ActiveSheet.PivotTables("PivotTable1").PivotCache.MissingItemsLimit = xlMissingItemsNone
    
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh[/FONT]

 

[pawest]

Domenic, I will add your suggestion of xlmissingitemsnone after I solve the selecting a blank range issue.

par60056, like you, I am working on a very large project. This project performs daily analysis that will pull more than 8 FTP files, import the CSVs into Excel, then run a series of analysis on them with code that I've already written. The data ranges contain as little as 3 lines of code and up to hundreds of thousands! Pretty fun stuff! The analysis includes creating 8 different pivot tables. I'm still getting hung up on this pivot table with no data issue. I'm now trying to select an additional blank line every time I create a pivot cache. Here's a snippet of my code on how I'm going about doing that:

Dim ptCache As PivotCache
Dim pt As PivotTable

Dim sData As Range

Dim finalRow As Integer

finalRow = Cells(Rows.count, 1).End(xlUp).Row
lastCol = Cells(1, Columns.count).End(xlToLeft).Column

' pivot table
'Select the data and add a blank row
Sheets("Transactions").Range("A1").Select
Sheets("Transactions").Range(Range("A1"), Cells(finalRow + 1, lastCol)).Select
Set sData = Selection

' Create the cache
Set ptCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=sData)

Two error messages have come up. One was about not being able to select the data. I realized this was because a range was already selected. So, I worked around that issue by Sheets("Transactions").Range("A1").Select and selecting A1. After I solved that issue, I'm not getting a Run-Time error '1004': Select method of Range class failed.

Any ideas?