+ Reply to Thread
Results 1 to 2 of 2

How can I skip a .PivotItem if it doesn't exist?

  1. #1
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,192

    How can I skip a .PivotItem if it doesn't exist?

    here are the two macros. One inserts the dates into the spreadsheet, the other references those cells to filter to pivot by. However, I always error out when there was a holiday the previous week because there was no work done on that day. I need the second macro to just skip that .PivotItem if it doesn't exist. Is there a way to say, if none then skip?



    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by taylorsm; 07-13-2018 at 03:43 PM.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: How can I skip a .PivotItem if it doesn't exist?

    Avoid the problem altogether and skip the VBA.

    First of all, convert your data to an Excel Table. Tables know how big they are an expand automatically, so formulas, charts and pivot tables that reference them always use the right amount of data. Also you can use the headers in formulas and VB code which makes them easier to understand. And finally, Excel tables copy down formulas, formats and validations automatically every time a row is added.

    In the attached I have two helper cells in Q1 and Q2. I named them Week_End and Week_Begin respectively.

    Then I put a helper column on the table with the formula: =AND([@[Date Reviewed]]>=Week_Beign,[@[Date Reviewed]]<=Week_End) - this is true if Date Reviewed is within the last week.

    Now you can use the Use Date header as a filter on the pivot table.

    -=o=-

    To answer the more generic question. Use a COUNTIF against what you want to set. If it is zero there is not data for that value. If it is greater than zero, then it has data and can be used as a pivot table filter.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Count cell if values exist else skip
    By sabeelahmedks in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-06-2018, 07:17 AM
  2. PivotItem loop with getpivotdata - hide pivotitem
    By csutera in forum Excel General
    Replies: 1
    Last Post: 01-14-2016, 10:41 AM
  3. VBA Code to Skip to Next Macro if File Doesn't Exist
    By ahankerson2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-11-2015, 01:44 AM
  4. [SOLVED] Macro Pivot Table: Most elegant way to skip a "do not display" if element doesn't exist
    By Skotzmun in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-22-2013, 05:03 AM
  5. Replies: 0
    Last Post: 06-14-2013, 12:33 PM
  6. [SOLVED] PivotItems in a PivotTable position -- Skip if PivotItem errors
    By bcn1988 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-28-2013, 10:03 AM
  7. Replies: 4
    Last Post: 06-18-2006, 01:10 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1