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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
determine cell is part of pivot item or pivot field
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
determine cell is part of pivot item or pivot field
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| +------------------------------------------------------ ----------+ ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
determine cell is part of pivot item or pivot field
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 ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
Reply |
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) |