Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to SUM entire column, but only when adjacent cell is not empty | Excel Worksheet Functions | |||
why a reference to an empty cell is not considered empty | Excel Discussion (Misc queries) | |||
in excel..:can't empty clip are" but already empty | Excel Discussion (Misc queries) | |||
Excel - Autom. Filter "Empty / Non Empty cells" should come first | Excel Discussion (Misc queries) | |||
Can blank cells created using empty Double-Quotes not be empty?? | Excel Programming |