or just change it to:

Public Function IsColumnEmpty(rngTest As Range) As Boolean
Dim lRow As Long
Dim nCol As Integer
nCol = rngTest.Column
With rngTest.Parent
lRow = .Cells(rows.count, nCol).End(xlUp).Row
If lRow = 1 And IsEmpty(.Cells(1, nCol).Value) Then _
IsColumnEmpty = True
End With
End Function

But if using as a UDF, might as well just use the countA function directly.

Regards,
Tom Ogilvy


Heiko wrote in message
...
Dear Robin,
thats good too
but only if one is using <= Excel 5 he has to modify the maximum
Rowcount.

Heiko
:-)
"Robin Hammond" wrote:

Paul,

This is one way to do it. I've used a range in the function to ensure
that
if this is used as a UDF it can reference another sheet if needed. If
your
in XL97 you'll need to change the line that sets lrow to 16384 (or
whatever
the max rows is).

Public Function IsColumnEmpty(rngTest As Range) As Boolean
Dim lRow As Long
Dim nCol As Integer
nCol = rngTest.Column
With rngTest.Parent
lRow = .Cells(65536, nCol).End(xlUp).Row
If lRow = 1 And IsEmpty(.Cells(lRow, nCol).Value) Then _
IsColumnEmpty = True
End With
End Function

Robin Hammond
www.enhanceddatasystems.com
Check out our XspandXL add-in


"Paul James" wrote in message
news:ULJWa.46245$uu5.5082@sccrnsc04...
I need to test an entire column to see if there are any non-blank cells
in
the column. I've tried writing an expression using CountA(), but I
can't
get it to work.

Can anyone tell me how I could test, say, Column C, to see if there are
any
blank cells in that column?

Thank you in advance.

Paul