=sign(--counta(range)) not working righ t

  • Thread starter Thread starter dlotz
  • Start date Start date
D

dlotz

I have been using the above function for the purpose of
telling me if there is any data in range (row of cells)

For some reason the function is still producing a 1
even with no vavlues numbers or chacters in the range.
I have tried several cell formats and for some resaon it
is not work, well sometime yes and sometime no

any suggestions
 
A formula such as =IF(A1>10,"","OK") will display nothing when A1 is greater
than 10
If this is in the range A2:D2 then =COUNTA(A2:D2) will return 1, even if all
the cells in the range look blank
best wishes
 
Maybe you have something in that range that you can't see--either space
characters, HTML non-breaking space characters, formulas that evaluate to "" or
values that were converted from formulas that evaluated to "".

What does:
=Counta(range)
return?
What is that range's address?
 
Exactly, I know there has to be something in the range.
I had to consolidate a few spreedsheet, bunch of copy and pasting.

I did the simple Counta, and it returned a 1
so there has to be somthing in range unseen.
The mystery remains
any suggestions
 
[email protected] said:
so there has to be somthing in range unseen. The mystery remains
any suggestions
Click to expand...

Perhaps the following macro will help. Select the range and execute the
following macro:

Sub findit()
Dim cell As Range
For Each cell In Selection
If Not IsEmpty(cell) Then MsgBox cell.Address
Next cell
End Sub

For each non-empty cell identified by the macro (your are expecting only
one), select the cell and go from there. First, look at its contents shown
in the Formula Bar. If it still appears empty, execute =CODE(A1) to see the
character that appears blank. Or simply press Delete to delete the cell
contents.

Note: To create the macro, press alt+F11 to open a VB window. In the VB
window, click Insert, then Module to open a VB Editor pane. Copy-and-paste
the macro text above into the VB Editor pane. Then return to the Excel
window, select the range, and press alt+F8 to select and run the macro.


----- original message -----
 
Back
Top