Is entire row empty?

S

soylent green

Hello,

I have a code presently that checks to see if an entire
row is blank by sending the cursor out to see if it hits
anything. It then returns the cell address and it checks
the column.

I'm positive there is a better way that is more concise.
Can someone supply me with the code to do it, preferably
in one line?

Thanks in advance for your help.
 
C

Chip Pearson

Try the following:

If Application.WorksheetFunction.CountA(ActiveCell.EntireRow) = 0 Then
Debug.Print "empty"
Else
Debug.Print "not empty"
End If



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
J

Jesse

The COUNTBLANK function may help.

It will count the number of empty cells in a range.
 
T

Tom Ogilvy

Didn't someone already suggest CountA and you were asking for an
alternative?

Regards,
Tom Ogilvy
 
A

Alan Beban

soylent said:
Can someone supply me with the code to do it

To do what? Return True or False, depending on whether it's blank?
Return the address if it's not? "Check the column", whatever that means?
Something else?

Alan Beban
 
D

Dana DeLouis

It probably doesn't matter, but just be aware that CountA counts "Prefix
Characters," and CountBlank does not.
 
A

Alan Beban

What are Prefix Characters? A brief Help search didn't seem to know what
they were.

Alan Beban
 
C

Chip Pearson

Alan,

I believe he's talking about the ' used at the beginning of a cell's data.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
A

Alan Beban

Thanks, Chip. I thought that might be part of it; but I didn't know if
there were others that everybody but me knew about <g>

By the way, =SUM(LEN(1:1))=0, array entered, will return TRUE or FALSE,
depending on whether the entire row is or isn't blank.

Alan Beban
 
D

Dana DeLouis

Hi Alan. Just thought I'd mention it because the op may wonder why the row
is not indicating all blank when it may "look" blank.
There's a little mention of prefix characters in VBA under PrefixCharacter.
Here, CountBlank shows D1 is "Blank," even though it has a Prefix character.

Sub Demo()
[D1] = "'" 'Chr(39)

Debug.Print WorksheetFunction.CountA([D1])
Debug.Print WorksheetFunction.CountBlank([D1])
Debug.Print HasPrefix([D1]) 'True
End Sub

Function HasPrefix(Rng As Range) As Boolean
HasPrefix = Len(Rng.PrefixCharacter)
End Function

returns...
1
1
True

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


Alan Beban said:
Thanks, Chip. I thought that might be part of it; but I didn't know if
there were others that everybody but me knew about <g>

By the way, =SUM(LEN(1:1))=0, array entered, will return TRUE or FALSE,
depending on whether the entire row is or isn't blank.

Alan Beban
data.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top