testing for non-empty cells

  • Thread starter Thread starter Paul James
  • Start date Start date
P

Paul James

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,

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,

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")
 
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
 
=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.
 
Back
Top