Excel "Blanks" Question

[tdpman]

Good Morning everyone. I have a small issue that I'm hoping someone has an easy fix to.

I have an Excel spreadsheet that is output from an Access database. I then do a pivot table on this spreadsheet. The problem that I am running into is that the pivot table does not recognize the blank cells as being blank. I actually have to autofilter each column by the blanks and delete the blanks before the pivot table will calculate correctly. Is there an easy fix to this or does anyone have any code that I could run that would basically do a clear contents on the blank cells?

Thanks for your help!

tp

 

[SkipVought]

Hi,

the AutoFilter method to condition the data in your table is about as good as you're going to find, unless you want to write a VBA procedure to replace spaces with "".



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[KenWright]

Try running Dave McRitchie's Trimall macro against your imported data, prior to doing the Pivot table. Not 100% sure, but it may do the trick:-

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Regards
Ken..................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------