Hi All,

I have a named range and I want to create a non-contiguous sub-range of it
depending on a criterium. Is there a more refined way of it than looping
through the cells of the range one by one and add cells meeting the criterium
to the sub-range with Union method?

Thanks,
Stefi

On Dec 7, 8:41 am, Stefi wrote:
Hi All,

I have a named range and I want to create a non-contiguous sub-range of it
depending on a criterium. Is there a more refined way of it than looping
through the cells of the range one by one and add cells meeting the criterium
to the sub-range with Union method?

Thanks,
Stefi

Hi
No - not really.
If it is a large range you could create a variant array first

rangeVariant = Range("myRange").Value

checking your criterium in this variant might speed things up a bit.
You could also create a text string of range addresses that meet the
criterium using concatenation, then do

Set CriteriumRange = Range(TextString)

but you run up against the 1024 character limit of a string inside
Range. That can be got round by doing a Union every so often, but
refined it isn't! I can dig out some code if you want to see it.

regards
Paul

Thanks Paul, I guessed it but I wanted to make sure before starting writing
code. It's not a large range, I can manage writing the code.

Regards,
Stefi


€� ezt Ã*rta:

On Dec 7, 8:41 am, Stefi wrote:
Hi All,

I have a named range and I want to create a non-contiguous sub-range of it
depending on a criterium. Is there a more refined way of it than looping
through the cells of the range one by one and add cells meeting the criterium
to the sub-range with Union method?

Thanks,
Stefi

Hi
No - not really.
If it is a large range you could create a variant array first

rangeVariant = Range("myRange").Value

checking your criterium in this variant might speed things up a bit.
You could also create a text string of range addresses that meet the
criterium using concatenation, then do

Set CriteriumRange = Range(TextString)

but you run up against the 1024 character limit of a string inside
Range. That can be got round by doing a Union every so often, but
refined it isn't! I can dig out some code if you want to see it.

regards
Paul