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

Hello Paul

Dim intNo as Integer
intNo =
Application.CountBlank(ActiveWorkbook.Worksheets(1 ).Range("C:C"))

Heiko
:-)
"Paul James" wrote:

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

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

Paul,

Doesn't =COUNTA(C:C) tell you if there are some non-blank cells. It picks up
formulae and values. The number of rows is 65536 (97 and above), so if it's
less than that number there are balnsk

=IF(COUNTA(C:C)<65536,"you have blanks","no blanks")

--

HTH

Bob Phillips

"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

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

=if(countA(C:C)=0,"All Blank","Blanks: "&rows(C:C)-CountA(C:C))

if you want to find blanks only up to the last used cell

=IF(COUNTA(C:C)=0,"All Blank","Blanks:
"&MATCH(9.99999999999999E+307,C:C)-COUNTA(C:C))

if only numeric values are contained in column C or for only non-numeric
entries:

=IF(COUNTA(C:C)=0,"All Blank","Blanks:
"&MATCH(REPT("Z",255),C:C)-COUNTA(C:C))

Actually, the restriction is only to the type of data in the last populated
cell.



--
Regards,
Tom Ogilvy


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

Great information.

Thank you, Heiko, Robin, Tom an Bob.