determine cell is part of pivot item or pivot field

[isskiran]

Hi, I am trying to delete all data in excel sheet which consists of
pivot table and data. I need to clear the data in the pivot table so
that I can be sure of clearing the cache.
i am looping through all the sheets and cells and deleting
data.however, when the loop encounters a cell which is part of pivot
field, it throws error saying that i cant assign null values to pivot
field. How do i check the condition that if a particular cell is a
pivot field or not? in that case, i can put a if-else condition and
skip those..please help me determine how to identify a cell whether it
is part of pivot field?

secondly, i have some custom formatting and custom grouping of fields
such as months,qtrs,years etc. when i call refresh method of pivotcache
object, it is removing all the groupings. (Assuming that I am manually
deleting all the data from the data sheet)
I want to keep the formatting and not remove the custom groupings.

please find the code attached.
Option Explicit
' Macro to loop through all the cells in the excel worksheet and delete
the
' data. This is done for all the worksheets.

Sub clearData()

On Error GoTo vbMacroError
Dim lngWorkSheet As Long
Dim lngLastUnsedRowCell As Long
Dim lngLastUnsedColCell As Long
Dim i As Long
Dim j As Long

' First loop through all the worksheets in the workbook
For lngWorkSheet = 1 To ActiveWorkbook.Worksheets.Count

' For each worksheet, find the last non-empty cell. This is
needed
' to get the range for looping the cells.
lngLastUnsedRowCell = ActiveWorkbook.Worksheets(lngWorkSheet)
_
UsedRange.Rows.Count
lngLastUnsedColCell = ActiveWorkbook.Worksheets(lngWorkSheet)
_
UsedRange.Columns.Count

Dim c As Variant
Dim lngCount As Long
Dim strLetter As String
strLetter = getAlphabet(lngLastUnsedColCell)
For Each c In
ActiveWorkbook.Worksheets(lngWorkSheet).Range("A1:" & strLetter &
lngLastUnsedRowCell)
'****I am getting the error here.***
c.Value = ""
nextblock:

Next c
Next lngWorkSheet
Exit Sub
vbMacroError:

MsgBox "Error occurred. Error Description is: " & Err.Description

End Sub

Function getAlphabet(lngNumber As Long)

getAlphabet = Switch(lngNumber = 1, "A", lngNumber = 2, "B", _
lngNumber = 3, "C", lngNumber = 4, "D", _
lngNumber = 5, "E", lngNumber = 6, "F", _
lngNumber = 7, "G", lngNumber = 8, "H", _
lngNumber = 9, "I", lngNumber = 10, "J", _
lngNumber = 11, "K", lngNumber = 12, "L", _
lngNumber = 13, "M", lngNumber = 14, "N", _
lngNumber = 15, "O", lngNumber = 16, "P", _
lngNumber = 17, "Q", lngNumber = 18, "R", _
lngNumber = 19, "S", lngNumber = 20, "T", _
lngNumber = 21, "U", lngNumber = 22, "V", _
lngNumber = 23, "W", lngNumber = 24, "X", _
lngNumber = 25, "Y", lngNumber = 26, "Z")
End Function


+----------------------------------------------------------------+
| Attachment filename: sample.xls |
|Download attachment: http://www.excelforum.com/attachment.php?postid=357533|
+----------------------------------------------------------------+

 

[Tom Ogilvy]

Just ignore the error

On Error Resume next
for each cell in Activesheet.UsedRange

cell.ClearContents

Next
On Error goto 0

If you delete the pivot table, you delete the settings. The grouping is
part of the pivot table definition, so when you create a new table, you need
to reestablish the group settings.

Deleting a pivot table does not clear the cache per se. If you close and
save the workbook and there is no pivottable that refers to that cache, the
cache will be removed as well.

 

[Tod]

If you are trying to just delete the pivottable, you can
do:

'If there is only one pivottable on the sheet.
ActiveSheet.PivotTables(1).TableRange2.Clear

You may consider creating code that deletes and recreates
your pivottable each time. That way you can set your
grouping, formats, etc. The first time someone suggested
this to me I thought they were nuts. But's actually
better. I now have over a dozen pivottable reports, some
with over 300,000 records, and they are all deleted and
recreated by code each day.

tod

-----Original Message-----

Hi, I am trying to delete all data in excel sheet which consists of
pivot table and data. I need to clear the data in the pivot table so
that I can be sure of clearing the cache.
i am looping through all the sheets and cells and deleting
data.however, when the loop encounters a cell which is part of pivot
field, it throws error saying that i cant assign null values to pivot
field. How do i check the condition that if a particular cell is a
pivot field or not? in that case, i can put a if-else condition and
skip those..please help me determine how to identify a cell whether it
is part of pivot field?

secondly, i have some custom formatting and custom grouping of fields
such as months,qtrs,years etc. when i call refresh method of pivotcache
object, it is removing all the groupings. (Assuming that I am manually
deleting all the data from the data sheet)
I want to keep the formatting and not remove the custom groupings.

please find the code attached.
Option Explicit
' Macro to loop through all the cells in the excel worksheet and delete
the
' data. This is done for all the worksheets.

Sub clearData()

On Error GoTo vbMacroError
Dim lngWorkSheet As Long
Dim lngLastUnsedRowCell As Long
Dim lngLastUnsedColCell As Long
Dim i As Long
Dim j As Long

' First loop through all the worksheets in the workbook
For lngWorkSheet = 1 To ActiveWorkbook.Worksheets.Count

' For each worksheet, find the last non-empty cell. This is
needed
' to get the range for looping the cells.
lngLastUnsedRowCell = ActiveWorkbook.Worksheets (lngWorkSheet)
_
.UsedRange.Rows.Count
lngLastUnsedColCell = ActiveWorkbook.Worksheets (lngWorkSheet)
_
.UsedRange.Columns.Count

Dim c As Variant
Dim lngCount As Long
Dim strLetter As String
strLetter = getAlphabet(lngLastUnsedColCell)
For Each c In
ActiveWorkbook.Worksheets(lngWorkSheet).Range("A1:" & strLetter &
lngLastUnsedRowCell)
'****I am getting the error here.***
c.Value = ""
nextblock:

Next c
Next lngWorkSheet
Exit Sub
vbMacroError:

MsgBox "Error occurred. Error Description is: " & Err.Description

End Sub

Function getAlphabet(lngNumber As Long)

getAlphabet = Switch(lngNumber = 1, "A", lngNumber = 2, "B", _
lngNumber = 3, "C", lngNumber = 4, "D", _
lngNumber = 5, "E", lngNumber = 6, "F", _
lngNumber = 7, "G", lngNumber = 8, "H", _
lngNumber = 9, "I", lngNumber = 10, "J", _
lngNumber = 11, "K", lngNumber = 12, "L", _
lngNumber = 13, "M", lngNumber = 14, "N", _
lngNumber = 15, "O", lngNumber = 16, "P", _
lngNumber = 17, "Q", lngNumber = 18, "R", _
lngNumber = 19, "S", lngNumber = 20, "T", _
lngNumber = 21, "U", lngNumber = 22, "V", _
lngNumber = 23, "W", lngNumber = 24, "X", _
lngNumber = 25, "Y", lngNumber = 26, "Z")
End Function


+------------------------------------------------------ ----------+
| Attachment filename: sample.xls |
|Download attachment: http://www.excelforum.com/attachment.php?postid=357533|

+------------------------------------------------------

----------+

------------------------------------------------
[/url]
~~View and post usenet messages directly from http://www.ExcelForum.com/

.

 

[isskiran]

Hi,

I have an excel file which is around 20 MB when there is data in th
data sheet. This data is used for the pivot tables in the same workboo
but different sheets.

1. I want to clear the cache of the pivot tables so that when I try t
get the data from the oracle database to the datasheet, I want th
latest and updated values in the pivot table.

2. I want to first remove all the data from the data sheet and reduc
the file size to under 1 MB. This skeleton file contains just the pivo
table definition and no data in the data sheet. When I remove the data
the file size is still 5 MB...any idea why it is occupying 5 MB withou
data?

3. How do I clear the pivot cache?

4. I have some custom formatting and custom groupings here. when
delete all the data, the groupings are gone and not recreated when th
data is loaded. how do i keep the groupings?

5. I have some pivotcharts also. How do I refresh them?

Sorry for so many questions but i am not getting any solutions righ
now.

Thanks for your time. If anyone knows a solution, please respond

regards
Kira