Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
determine cell is part of pivot item or pivot field
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/ |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Help Using Calculated Field or Item in Pivot Table | Excel Worksheet Functions | |||
How to make a field created a part of the Pivot Table Field List? | Excel Discussion (Misc queries) | |||
Pivot field dropping one item | Excel Discussion (Misc queries) | |||
Calculated Field/Item in a Pivot | Excel Worksheet Functions | |||
Pivot Table Calculated field item | Excel Discussion (Misc queries) |