Glad you found a solution.

Watch out for cells with multiple space characters.

You may want to run a macro like:

Option Explicit
Sub testme()
Dim sCtr As Long
With Worksheets("Sheet999")
For sCtr = 1 To 20
.Cells.Replace what:=Space(sCtr), _
replacement:="", _
lookat:=xlWhole, _
searchorder:=xlByRows, _
MatchCase:=False
Next sCtr
End With
End Sub

Choose a number that's larger than the worst case you can imagine.



rc wrote:

Yee Haw! That did the trick. There was a single space in each of the
"empty" cells.

I used option#1 below on the whole worksheet, but used "match whole cell"
option to not remove the spaces in the other cells with text.

Thanks for your help Dave...it is greatly appreciated.

rc

"Dave Peterson" wrote:

Nope. It's only counting cells that are filled.

The bad news is that you're looking at cells that look empty, but aren't.

Try this:
Pick out one of those cells (say x9)
put this in an empty cell:
=len(x9)
what do you see?
If it returns a number larger than 0, then you may have spaces in those
cells--or something that looks like a space.

If it returns 0, then those cells could have held formulas that evaluated to ""
(looked empty).

Depending on the problem (and the data), you may have a couple of choices.

#1. If they're space characters and no other space characters should be in any
of the other cells (that's important!).

Select the range
edit|replace
what: (space character)
with: (leave blank)
replace all

Then try the =counta() to see if it's fixed.

#2. If it's the result of a formula converted to values:
Select the range
edit|Replace
what: (leave blank)
with: $$$$$
replace all

followed by:
edit|Replace
what: $$$$$
with: (leave blank)
replace all

This should clean up that detritus. But check it with =counta().

Then run the macro.

#3. You copied and pasted from a web page. And those funny non-breaking html
characters are in your cells.

David McRitchie has a macro that cleans up this kind of stuff:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")



rc wrote:

When using the formula you reccomend below on the fifty alarm columns for a
lot that only has a single alarm in ALM1 column, I get a return value of 50.
So sounds like it is counting these empty cells the same as the populated
cells???

Thanks,
rc


--

Dave Peterson