Selecting 40 cells in one moment

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

--
Message posted from http://www.ExcelForum.com

0
9/14/2004 2:10:44 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
419 Views

Similar Articles

[PageSpeed] 21

Hi

>> I suppose the range string is too long
Yes

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


-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"SiriS >" <<SiriS.1cktru@excelforum-nospam.com> wrote in message news:SiriS.1cktru@excelforum-nospam.com...
> Hello,
> I'm using excel 2002 and windows 2000 professional.
>
> I am trying to dynamically create a "range string" for a range that I
> would like to select in a sheet. The range sometimes consists of 40
> cells (not always following each other).
>
> With a range string as in the following example I get an run-time error
> 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 two
> parts? If so, how to select more than one range area at a moment?
>
> Thanks,
> Siri
>
>
> ---
> Message posted from http://www.ExcelForum.com/
> 


0
rondebruin (3790)
9/14/2004 2:34:31 PM
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

--
Message posted from http://www.ExcelForum.com

0
9/15/2004 6:30:21 AM
"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

--
Message posted from http://www.ExcelForum.com

0
9/15/2004 7:41:51 AM
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

--
Message posted from http://www.ExcelForum.com

0
9/15/2004 8:31:49 AM
Reply:

Similar Artilces: