determine if cells are empty

[johannes2008]

Hello All

I was wondering if anyone had any ideas on how to code for this:

What I need is to check if 4 cells in a row (all next to each other) are completely empty (no formulas, no numbers, no text). The row number is changing after every check white a Do/Until loop. In fact the loop will continue until it encounters a row where all 4 cells are empty

so what I am trying to use, that isn't working, is:
Loop Until ActiveCell.Offset(z, x + 1) = "" & ActiveCell.Offset(z, x + 2) = "" & ActiveCell.Offset(z, x + 3) = "" & ActiveCell.Offset(z, x + 4) = ""


any help?

 

[Andrew Poulsom]

Try:

Loop Until WorksheetFunction.CountA(ActiveCell.Offset(z, x + 1).Resize(4)) = 0

 

[Rick Rothstein]

What about this...

Loop Until WorksheetFunction.CountBlank(ActiveCell.Offset(z, x + 1).Resize(, 4)) = 4

 

[johannes2008]

Hey Andrew

I tried your code and it works perfect THANKS!

-Marc

 

[Andrew Poulsom]

Hey Andrew

I tried your code and it works perfect THANKS!

-Marc

Well it shoudn't because I missed a comma in resize:

Loop Until WorksheetFunction.CountA(ActiveCell.Offset(z, x + 1).Resize(,4)) = 0

 

[Rick Rothstein]

Hey Andrew

I tried your code and it works perfect THANKS!

Did it work "as posted"? I ask because your code snippet showed the 4 cells you wanted to test were next to each other along the same row, not down down a column... Andrew's Resize specifies 4 cells in a column... you would need Resize(,4) to specify 4 cells along a row (note the comma).

 

[johannes2008]

Well it worked, but not right. Now with the comma it does work right.

I was wondering if you could tell me what CountA does so maybe I can use it in the future.

Thanks
-Johannes

 

[Andrew Poulsom]

The COUNTA function counts the number of cells that are not empty.

 

[johannes2008]

ok. thanks for all the help.