Is entire row empty?

  • Thread starter Thread starter soylent green
  • Start date Start date
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.
 
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
 
Didn't someone already suggest CountA and you were asking for an
alternative?

Regards,
Tom Ogilvy
 
soylent said:
Can someone supply me with the code to do it
Click to expand...

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
 
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
 
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
Click to expand...
data.
 
Back
Top