Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Sep 2001
    Location
    Rochester, New York, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    PivotTable Catagory fields (2000 SR-1, & XPsp-2)

    Hi, In my PivotTables and PivotCharts I use fields that contain "catagories" like salesmen names, Month, Year. In both the tables and charts these catagories are pull/dropdowns with check boxes. Everything functions until I rebuild the "list" the tables and charts are based on. The lists has the same number of columns/fields but may have new names and some of the old ones are no longer present. PROBLEM: The deleted catagories names still show in the drop down list! The new catagories show as expected. I can't find a way to clean these up. Any ideas?
    Regards Rod

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: PivotTable Catagory fields (2000 SR-1, & XPsp-2)

    Did you remove them from the dataset the pivot is based on. The lists are the items from the raw data. If the names are in the data, they will be in pulldown lists.

    Steve

  3. #3
    New Lounger
    Join Date
    Sep 2001
    Location
    Rochester, New York, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PivotTable Catagory fields (2000 SR-1, & XPsp-2)

    The excel list is erased and replaced by a current set of data from the company database. Any new names are are added to the drop downs BUT any names that are removed from the excel list are still showing in the pull downs.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: PivotTable Catagory fields (2000 SR-1, & XPsp-2)

    Have you refreshed the pivot table?

    Steve

  5. #5
    New Lounger
    Join Date
    Sep 2001
    Location
    Rochester, New York, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PivotTable Catagory fields (2000 SR-1, & XPsp-2)

    Steve, Yes I have.

    Rod

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: PivotTable Catagory fields (2000 SR-1, & XPsp-2)

    I finally figured out what you are referring to! In XL97 the only pulldowns are in the page field. But in the Pivot Table Field dialog box there is a "used Items" list which acts like you described: new items are added but unused "empty" items are not cleared. (I presume that in later version of XL they made this list a pulldown and do not require the dialog box to get to it)

    This code will loop through all the worksheets, loop thru all the pivot tables, loop thru all the fivot fields, loop thru the items and if there is none of that item it will delete it.

    Hope this helps,

    Steve

    <pre>Option Explicit
    Sub DeletePTCategories()
    Dim wks As Worksheet
    Dim PT As PivotTable
    Dim PF As PivotField
    Dim PI As PivotItem

    For Each wks In ActiveWorkbook.Worksheets
    For Each PT In wks.PivotTables
    PT.RefreshTable
    For Each PF In PT.VisibleFields
    For Each PI In PF.PivotItems
    If PI.RecordCount = 0 Then PI.Delete
    Next
    Next
    Next
    Next
    End Sub</pre>


  7. #7
    New Lounger
    Join Date
    Sep 2001
    Location
    Rochester, New York, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PivotTable Catagory fields (2000 SR-1, & XPsp-2)

    Steve,
    Thanks a million. Works like a charm!
    I had hunted MS Knowledge base and couldn't find anything about this problem. 'Course my luck at finding any relevant info there is very low. Thanks again.
    Rod VanHorn

  8. #8
    New Lounger
    Join Date
    Sep 2001
    Location
    Rochester, New York, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PivotTable Catagory fields (2000 SR-1, & XPsp-2)

    Steve,
    Have been using your fix for the "no longer in the data set" category items that showed up in the combo boxes. Works great but I have run into another snag. Our named-range for the variable sized data set, goes down to 10,000+ rows. We clean out the data and import new data into this range. The data may only be 1,500 rows. The macro comes up with:

    "Run-time error '1004' Application-defined or object-defined error".
    Debug highlights PI.Delete in the last part of code string "If PI.RecordCount = 0 Then PI.Delete"

    If I re-define the named-range to just include data without any empty rows the macro works fine.
    Is there any way I can use open ended named-ranges and still clean out the combo boxes?

    Has Microsoft got any fixes?

    Thanks guys, Rod

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: PivotTable Catagory fields (2000 SR-1, & XPsp-2)

    This should fix the problem:
    Add another condition to check for the "blank entry"

    <pre>If PI.RecordCount = 0 And PI.Name <> "(blank)" Then PI.Delete</pre>


    Another solution is to modify the named range.
    You might consider creating a dynamic range name that changes size to fit the data using offset.
    create 3 names on the sheet (eg assume it is the only thing in sheet 1 and starts in A1
    Insert - name - define

    Name: CountCols
    refers to: (no quotes)
    "=COUNTA(Sheet1!$1:$1)"

    Name: CountRows
    refers to: (no quotes)
    "=COUNTA(Sheet1!$A:$A)"

    Name: DataTable
    refers to: (no quotes)
    "=OFFSET(Sheet1!$A$1,0,0,CountRows,CountCols)"

    Now your pivot table "DataTable" (or whatever you name it) range will always be as long as the data row count and as wide as the column count.

    NOTE: This assumes there are no blanks in row 1 or col A (except those outside the dataset)

    If you always know the number of columns and only the rows expand, instead of counting the cols, you can just enter that number and only create 2 named ranges.
    "=OFFSET(Sheet1!$A$1,0,0,CountRows,6)"

    Change sheetname as appropriate and change the name to the range you named it.

    This method shuld also cut down on the calc time since you only calculate the pivot in the smaller range.
    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •