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!

 

[pawest]

Please disregard the above code.... it should be:

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)

 

[par60056]

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?

With big things like this, I try to avoid selecting anything I don't have to. Any selection causes screen updates which slow things. Also turn off calculation when adding formulas to all the rows and then recalculate everything when done.

I specify my pivot table source directly. I don't see an advantage in naming a range just to use it once. (somebody may inform me I'm wrong)

So I have a function to convert a column number to the appropriate letters and specify my pivot source like this:
SourceData:= "rawData!A1:" & colText(lastCol) & lastRow

You will find many routines for converting a column number to the letters. However I recently found that none of them deal well with 3 character columns and one of my reports goes out to column BZF. So here is a more general converter that I wrote. There may be others that are better but I didn't find any and this works.

Function colText(ByVal colNum As Integer) As String
Dim ct As String


'  I know there are some irrelevant calculations in here but it made it easier to comprehend the
'  math to treat each letter the same.
'
'  The uncommented code is the same with the extra math steps removed


'ct = ""
'ct = Chr((((colNum - WorksheetFunction.Power(26, 0)) \ WorksheetFunction.Power(26, 0)) Mod 26) + Asc("A"))
'colNum = colNum - (((((colNum - 1) \ WorksheetFunction.Power(26, 0)) Mod 26) + 1) * WorksheetFunction.Power(26, 0))
'If colNum >= WorksheetFunction.Power(26, 1) Then
'    ct = Chr((((colNum - WorksheetFunction.Power(26, 1)) \ WorksheetFunction.Power(26, 1)) Mod 26) + Asc("A")) & ct
'    colNum = colNum - (((((colNum - 1) \ WorksheetFunction.Power(26, 1)) Mod 26) + 1) * WorksheetFunction.Power(26, 1))
'End If
'If colNum >= WorksheetFunction.Power(26, 2) Then
'    ct = Chr((((colNum - WorksheetFunction.Power(26, 2)) \ WorksheetFunction.Power(26, 2)) Mod 26) + Asc("A")) & ct
'End If
'
'colText = ct


ct = ""
ct = Chr(((colNum - 1) Mod 26) + Asc("A"))
colNum = colNum - (((colNum - 1) Mod 26) + 1)
If colNum >= 26 Then
    ct = Chr((((colNum - 26) \ 26) Mod 26) + Asc("A")) & ct
    colNum = colNum - (((((colNum - 1) \ 26) Mod 26) + 1) * 26)
End If
If colNum >= 676 Then
    ct = Chr((((colNum - 676) \ 676) Mod 26) + Asc("A")) & ct
End If


colText = ct
End Function

 

[par60056]

I also noted that you said

data ranges contain as little as 3 lines of code and up to hundreds of thousands!

Rownumber variables should ALWAYS be declared Long. With "finalRow as Integer" you cannot handle a sheet with more than 32767 rows of raw data.

 

[pawest]

Thanks to you guys, it's working!

However, it used to default to xlsum, meaning all the pivot tables where sum of value. Now, it's defaulting to count of value. I need it to default to xlsum.

I would like to add an all-encompassing For Each, nested If statement to perform: for each pt in ptSheet, if values are xlsum, do nothing, if not values xlsum, change to xlsum.

I'll work on this now and repost code later, but if anyone has any further insight on this, please let me know! Thanks.

 

[par60056]

That is the 1 issue with adding the blank row. Because there is a non numeric value the default becomes some.

I assume your code is adding all the fields into the pivot table.

Set ptCache = ActiveWorkbook.PivotCaches.Create( _ 


with ptCache
        With .PivotFields("Period")
            .Orientation = xlPageField
            .position = 1
        End With
        With .PivotFields("Market")
            .Orientation = xlRowField
            .position = 1
        End With



        .AddDataField .PivotFields("Dollars"), "tDollars", xlSum
        With .PivotFields("tDollars")
            .position = 1
            .NumberFormat = "$#,##0.00"
        End With

 

[pawest]

Good suggestion, Par60056. I'm going to explicitly make each data field xlsum using a with/end with statement. Maybe not the most efficient, but it's effective. Once I finish this project, I will work to further optimize it.

Thanks for all the help.

 

[par60056]

Generally I build the pivot table once and then spend an hour generating reports from it.

I'll trade a small amount of inefficiency in building the table for clearly understanding the building of the table.

I work hard to optimize the reporting to eliminate excess screen updating and formula calculation.