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/