I tried it directly on the worksheet and it worked ok (xl2003).

I tried this code:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim myRng As Range

Set wks = Worksheets.Add
With wks
Set myRng = .Range("a1:a10")
With myRng
Debug.Print "Empty: " & Application.CountBlank(.Cells)
myRng.Formula = "="""""
Debug.Print "formulas evaluating to """": " _
& Application.CountBlank(.Cells)
.Value = .Value
Debug.Print "after conversion to ' " _
& Application.CountBlank(.Cells)
End With
End With

End Sub

And got this:

Empty: 10
formulas evaluating to "": 10
after conversion to ' 10

And even though the help says that it counts empty cells, there's a remark that
says:

Cells with formulas that return "" (empty text) are also counted. Cells with
zero values are not counted.


Myrna Larson wrote:

Hi, Dave:

In some brief testing that I just did, a cell containing a formula that
returns "" is not considered to be blank. The cell has to be empty to be
included by COUNTBLANK.

If you have a column that contains a formula that returns either text or "",
you can count the number of "non-blank looking" cells with

=COUNTIF(M28:M1000,"""")

If the formula returns either a number or "":

=COUNT(M28:M1000)

Translating those to code, I come up with

If Application.COUNTIF(Range("M28:M1000"),"""""") 0 Then

and

If Application.COUNT(Range("M28:M1000")) < 0 Then

On Sat, 19 Feb 2005 19:19:31 -0600, Dave Peterson
wrote:

How about looking at the number of blanks (cells that are empty or evaluate
to
""):

With Range("M28:m1000")
If Application.CountBlank(.Cells) = .Cells.Count Then
MsgBox "all look blank"
Else
MsgBox "something looks like it's there"
End If
End With

Pat wrote:

If WorksheetFunction.CountA(Range("M28:M1000")) 0 Then

Because each cell in the range contain a formula the line of code will
always return true regardless if no result has been returned by any of the
formulas. Can anyone tell me if the code can be changed to only return true
if there is a result in any of the cells.

Thank U
Pat

--

Dave Peterson