Can the CountA range be a variable?

[Richard2012]

I'm trying to identify ranges that contain a certain number of non-blank cells and take some action if that number is certain value.
Here's an example: I've written this several ways but I can't get the CountA range to accept a variable.
Is this possible?

Please Login or Register  to view this content.

[jetted]

Not to pretty but should do the job, I am still looking why it does work, if I find something will report back

Please Login or Register  to view this content.

[Paul]

The issue stems from the fact that the CountA function is expecting a Range object, but when you concatenate "Block" with the variable "i" you're creating a String. Simply wrapping Range() around it doesn't convert it to an actual range, either, as no named range exists in the workbook (only in the code itself).

Perhaps try:

Please Login or Register  to view this content.

Change your sheet name and ranges as necessary.