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.

--
Regards,
Tom Ogilvy


"isskiran" wrote in 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|
+----------------------------------------------------------------+

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/