Is entire row empty?
 

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.

Is entire row empty?
 

Try the following:

If Application.WorksheetFunction.CountA(ActiveCell.En tireRow) = 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


"soylent green" wrote in message
...

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.

Is entire row empty?
 

The COUNTBLANK function may help.

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


"soylent green" wrote in message
...

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.

Is entire row empty?
 

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

Regards,
Tom Ogilvy

"soylent green" wrote in message
...

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.

Is entire row empty?
 

soylent green wrote:
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

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.

Is entire row empty?
 

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

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


"soylent green" wrote in message
...

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.

Is entire row empty?
 

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

Alan Beban

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

Is entire row empty?
 

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

"Alan Beban" wrote in message
...
What are Prefix Characters? A brief Help search didn't seem to know what
they were.

Alan Beban

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

Is entire row empty?
 

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

Chip Pearson wrote:
Alan,

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

Is entire row empty?
 

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" wrote in message
...
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

Chip Pearson wrote:
Alan,

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

Is entire row empty?
 

Thanks, Dana.

Alan Beban

Dana DeLouis wrote:
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