I'm working with Office 2003. Sometimes I have to download survey results from an online form. Whenever I do that, the COUNT functions mess up if there are blanks. For example, the COUNTA function will count the blank cells until I manually click at each one of them. The the result of the COUNTA function will change. I can't do a replace either because there is nothing to replace. I wonder if anyone else has encountered this problem before?

It sounds as if Excel doesn't think the cells are really blank although I don't know why clicking on them would change that. There are many hidden characters that could be in the cells, but again, I don't know why clicking on them would make the hidden characters go away. Have you tried to use =LEN() on the cells before you click on them to see if they return something other than zero? If they do then there are hidden characters in the cells that you might be able to replace with nothing. Any chance of sending me a copy of the spreadsheet, or even just a range of cells that exhibits this problem? If you'll either turn on Private Message or send me a PM, I'll respond with an email address that you can send the spreadsheet to.

DerbyDad03, thanks for your reply. I tried Len() but it only returned zero. I couldn't replace anything because when I clicked into the cell it turned it into a blank cell and when I copied the whole cell there was nothing to paste. I know there's something in it because multiplying two of those cells gives me an error. I would love to send you the spreadsheet to have a look at it. It seems that I cannot send you a private message here since I started the thread. So here is my email address: hupuo@yahoo.com. Thank you so much!

Dunno if it's worth a try, but what do you get when you do =COUNTA(A1:A10)-SUMPRODUCT(--(A1:A10=""))

@ jon k: OK, I'm intrigued...what are you looking for with the formula you suggested? With A1:A10 empty I get -10, with 1 entry I get -8, 2 yields -6, 10 yields 10. Please explain your thought process. Thanks! @ itsfriday: I sent you an email address via Private Message.

jon k: I got -4. The excel sheet was downloaded from an online survey form. Most fields are optional so respondents can leave then empty. Thus not all cells are affected. DerbyDad03: I've sent you the spreadsheet. Thanks!

Thanks for sending me the spreadsheet. The issue with your cells is that they are not empty. One of the Remarks in the Excel Help files for COUNTA states: - A value is any type of information, including error values and empty text (""). A value does not include empty cells. If you set a Watch on c and d in the VBA editor and step through this code, you see that I132 (one of your problem cells) contains "" which is empty text, but A147 ( a "true blank" as you called it) contains . (You'll notice I didn't type anything there, because that's what VBA returns.) Sub WhatsThere() Set c = Range("I132") Set d = Range("A147") End Sub Another test is to put a zero in a blank cell, copy it and do a "Paste Special...Add" to cells I4:I143. It won't put a zero in any cells that contain empty text since you can't add a number to text. So, how do you solve your problem? You could run this code against the sheet: Sub MakeTrueBlanks() Dim MyCell As Range Application.ScreenUpdating = False For Each MyCell In ActiveSheet.UsedRange If MyCell.Value = "" Then MyCell.Clear Next MyCell Application.ScreenUpdating = True End Sub Good luck!