[RESOLVED] Limit on Range().Select!???

ok.. I keep getting one step forward only to find another problem!!!

I am scanning a sheet.. adding all the cell ranges together.. then I need to highlight them...

in one test
Range("A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,A25,A2 6,A27").select
worked fine..

but when I tried:
A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,A25,A26,A27,A 28,A29,A30,A31,A32,A33,A34,A35,A36,A37,A38,A39,A40,A41,A42,A43,A44,A45,A46,A47,A48,A49,A50,A51,A52,A 53,A54,A55,A56,A57,A58,A59,A60,A61,A62,A63,A64,A65,A66,A67,A68,A69,A70,A71,A72,A73,A74,A75,A76,A77,A 78,A79,A80,A81,A82,A83,A84,A85,A86,A87,A88,A89,A90,A91,A92,A93,A94,A95,A96,A97,A98,A99,A100,A101,A10 2,A103,A104,A105,A106,A107,A108,A109,A110,A111,A112,A113,A114,A115,A116,A117,A118,A119,A120,A121,A12 2,A123,A124,A125,A126,A127,A128,A129,A130,A131,A132,A133,A134,A135,A136,A137,A138,A139,A140,A141,A14 2,A143,A144,A145,A146,A147,A148,A149,A150,A151,A152,A153,A154,A155,A156,A157

no good...
there is a Union function that allows you to union a bunch of ranges to select them but I cant get it to work?

1) how can I select all these at once (Without converting to a range.. unless someone can figure that one out ;) )
2) or How can I select 1 cell...but then ADD to the selection?

thanks

You can join the multiple ranges with the union function. As shown from the help file...

VB Code:

Dim r1 As Range, r2 As Range, myMultiAreaRange As Range
Worksheets("sheet1").Activate
Set r1 = Range("A1:B2")
Set r2 = Range("C3:D4")
Set myMultiAreaRange = Union(r1, r2)
myMultiAreaRange.Select

So if you were to create multiple ranges and add them together it should be possible. Perhaps a main range object and then in a loop or something Union the secondary range to the main one. Then clear the secondary or set to the next range and so on...

Are the cell names in you list always contiguous?

If so, then why not just take the 1st and last values?

If not, then you could use the Areas property of the Range object and look for contiguous sets of cells - using the 1st and last values of each set to add another area.

Perfect Rob.. Thanks!!!!