Consulting

Results 1 to 7 of 7

Thread: Continued: pivot table counting blank cells in data table

  1. #1

    Continued: pivot table counting blank cells in data table

    Hi all,

    I'm still battling with a problem.

    I have a pivot table which is counting blank cells from the range of data that the pivot table is reading from.

    It appears some of the cells have spaces in them so they appear blank in the data range but the pivot table is counting them.

    Is their a way I can clear these cells with spaces in, so the pivot table reads them as blank?

    Many thanks,

    Nicko

  2. #2
    VBAX Mentor MaximS's Avatar
    Joined
    Sep 2008
    Location
    Stoke-On-Trent
    Posts
    360
    Location
    before you creating pivot table you can use excel function:

    =trim(A1)

    this will remove extra spaces at the front or at the end of string

    then copy/paste special as values

    and bit of descending sorting will leave blanks to the bottom.

  3. #3
    VBAX Regular
    Joined
    Sep 2008
    Posts
    36
    Location
    If the cells with "real" values are all numeric, you can clear the range of spaces like this:

    Selection.SpecialCells(xlCellTypeConstants, 2).ClearContents

    The "2" parameter selects Text constants.

    S


    Quote Originally Posted by nicko
    Hi all,

    I'm still battling with a problem.

    I have a pivot table which is counting blank cells from the range of data that the pivot table is reading from.

    It appears some of the cells have spaces in them so they appear blank in the data range but the pivot table is counting them.

    Is their a way I can clear these cells with spaces in, so the pivot table reads them as blank?

    Many thanks,

    Nicko

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,313
    Location
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5

  6. #6
    Quote Originally Posted by Slyboots
    If the cells with "real" values are all numeric, you can clear the range of spaces like this:

    Selection.SpecialCells(xlCellTypeConstants, 2).ClearContents

    The "2" parameter selects Text constants.

    S
    the cells with real values are all text. Is there a way to do the same thing but leave the text cells and still clears the other cells?

  7. #7
    ok, is there an obvious problem with this...

    Selection.SpecialCells(xlCellTypeBlanks, 4).ClearContents
    what i am trying to do is clear the contents of all the blank cells

Posting Permissions

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