Selecting 40 cells in one moment

[SiriS]

Hello,
I'm using excel 2002 and windows 2000 professional.

I am trying to dynamically create a "range string" for a range that
would like to select in a sheet. The range sometimes consists of 4
cells (not always following each other).

With a range string as in the following example I get an run-time erro
message during selection:

dim myRangeString as String

myRangeString="$J$15,$L$15,$N$15,$P$15,$Q$15,$S$15,$T$15,$U$15,$V$15,$W$15,$X$15,$Y$15,$Z$15,$AA$15,$AB$15,$AC$15,$AD$15,$AE$15,$AF$15,$AG$15,$AH$15,$AI$15,$AJ$15,$AK$15,$AL$15,$AM$15,$AN$15,$AO$15,$AP$15,$AQ$15,$AR$15,$AS$15,$AT$15,$AU$15,$AV$15,$AW$15,$AX$15,$AY$15,$AZ$15,$BA$15"

Range(myRangeString).Select


Why?

I suppose the range string is too long. Do I have to separate it in tw
parts? If so, how to select more than one range area at a moment?

Thanks,
Sir

 

[Ron de Bruin]

Hi
Yes

This is the same in your example
myRangeString = "$J$15,$L$15,$N$15,$P$15,$Q$15,$S$15:$BA$15"

 

[SiriS]

Hi,
yes, that is the same thing. But my problem is the string is create
during runtime, and there is no guarantee that the cells are locate
after each other.

So I wonder if it is possible to cut the string into parts and selec
the different parts at the same time? Is it possible to select mor
than one range at one moment? Manually, yes I know (use Ctrl and selec
different cells), but with VBA code?

Thanks,
Sir

 

[popgroove]

"Union" is a way to join multiple range strings into one. I think yo
can fit around 30 cells in one string, but have never counted. A quic
example with 3 short strings:

Union(Range("A1,A3"), Range("C1,C3"), _
Range("E1,E4")).Activate


www.automateexcel.co

 

[SiriS]

Thank you, that really helped me!

Now I create two shorter rangestrings instead of the long one and i
works fine:

Union(Range(myRangeString1), range(myRangeString2)).Select

Thanks,
Sir