View Full Version : Continued: pivot table counting blank cells in data table
nicko
09-23-2008, 07:39 AM
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
MaximS
09-23-2008, 10:33 AM
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.
Slyboots
09-23-2008, 11:11 AM
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
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
Bob Phillips
09-23-2008, 11:39 AM
Cross-posted at OzGrid http://www.ozgrid.com/forum/showthread.php?t=108709
dominicb
09-23-2008, 12:36 PM
Cross-posted at OzGrid http://www.ozgrid.com/forum/showthread.php?t=108709
Also cross posted here(twice)
http://www.excelforum.com/excel-general/656385-pivot-table-counting-blank-cells-in-data-table.html
http://www.excelforum.com/excel-general/656497-pivot-table-counting-blank-cells-in-data-table-continued.html
DominicB
nicko
09-24-2008, 06:26 AM
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?
nicko
09-24-2008, 07:07 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.