Range Selection Limit ---??? - VBA

[spydey]

So, I am trying to write some simple code to select a specific set of cells to center & merge them. It is quite a large set of cells. When I input the cells in my code, I get an error 400 message. I have narrowed the issue down to everything after the the J column set of the cells selected. But I can't figure out why it won't allow me to select the whole set that I need.

My range of cells is :

B23:B24,B25:B26,B27:B28,B29:B30,B33:B34,B35:B36,B37:B38,E8:E9,E10:E11,E23:E26,E27:E28,E29:E30,E31:E32,E33:E34,E35:E36,H24:H25,H26:H27,H28:H29,H30:H31,H32:H33,H34:H35,H36:H37,I24:I25,I26:I27,I28:I29,I30:I31,I32:I33,I34:I35,I36:I37,J24:J25,J26:J27,J28:J29,J30:J31,J32:J33,J34:J35,J36:J37,K24:K25,K26:K27,K28:K29,K30:K31,K32:K33,K34:K35,K36:K37,L24:L25,L26:L27,L28:L29,L30:L31,L32:L33,L34:L35,L36:L37,M24:M25,M26:M27,M28:M29,M30:M31,M32:M33,M34:M35,M36:M37,N24:N25,N26:N27,N28:N29,N30:N31,N32:N33,N34:N35,N36:N37,O40:O41

My simple code so far is this:

Sub Merge()


    Range("B23:B24,B25:B26,B27:B28,B29:B30,B33:B34,B35:B36,B37:B38,E8:E9,E10:E11,E23:E26,E27:E28,E29:E30,E31:E32,E33:E34,E35:E36,H24:H25,H26:H27,H28:H29,H30:H31,H32:H33,H34:H35,H36:H37,I24:I25,I26:I27,I28:I29,I30:I31,I32:I33,I34:I35,I36:I37,J24:J25,J26:J27,J28:J29,J30:J31,J32:J33,J34:J35,J36:J37,K24:K25,K26:K27,K28:K29,K30:K31,K32:K33,K34:K35,K36:K37,L24:L25,L26:L27,L28:L29,L30:L31,L32:L33,L34:L35,L36:L37,M24:M25,M26:M27,M28:M29,M30:M31,M32:M33,M34:M35,M36:M37,N24:N25,N26:N27,N28:N29,N30:N31,N32:N33,N34:N35,N36:N37,O40:O41
").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge


End Sub

I have tested each selection, painstakingly going through each letter group, adding onto the previous letter groups. It all works as expected until I get to J30:J31. It then breaks there and nothing else after that will work. The only data I have in the first cell of each pair is the word: Testing. The exception being E23:E26, where E23 has the word but I am merging and centering E23:E26, so not just two cells.

Any pointers, thoughts, ideas?

I thought about using Union but wanted to figure this out first to help direct me to where I should be heading.

Thanks!

-Spydey

P.S. I also tried to select each cell and give them a named range, for ease of use, but for some reason, even though the range includes all the cells, the end result stops at the H set of cells. Weird!

 

[SpillerBD]

255 characters....

 

[RoryA]

Hi,

There is a limit of 255 characters for the address that you pass to the range property. What you can do is just break it up into two parts and then use Union to join them together like this:

Union(Range("first half of the addresses"), Range("rest of the addresses").Select

Also note that you don't actually need to select the cells for this.

 

[Gerald Higgins]

Deleted

 

[Fluff]

There is a limit of 255 characters in the range address, hence your problem.
You could split the ranges down & use union.
However I would warn against using merged cells, as they can cause major problems.


Added:
Thinking about it, not sure Union will work.
because I think that these cells will all be merged into 1 cell

"B23:B24,B25:B26,B27:B28,B29:B30

 

[spydey]

@ Gerald Higgins

Wouldn't that merge and center B23 through B30, then B33 through B38, etc etc?

What I need to do is merge B23 & B24, then merge B25 & B26, then B27 & B28, etc etc.

-Spydey

 

[spydey]

Hi,

There is a limit of 255 characters for the address that you pass to the range property. What you can do is just break it up into two parts and then use Union to join them together like this:

Union(Range("first half of the addresses"), Range("rest of the addresses").Select

I had thought about using union, but was concerned about it possible not merging the correct cells. I haven't tried it yet. I will give it a shot and let you know.

Also note that you don't actually need to select the cells for this.

What do you mean by this? How can I merge the cell groups that I need if I don't select them? Maybe I don't understand what you are referring to ..... :D

-Spydey

 

[Gerald Higgins]

Wouldn't that merge and center B23 through B30, then B33 through B38, etc etc?

Yes it would, sorry, I didn't read your original post properly, I have now deleted my response.
Apologies for any confusion !

 

[spydey]

There is a limit of 255 characters in the range address, hence your problem.
You could split the ranges down & use union.
However I would warn against using merged cells, as they can cause major problems.


Added:
Thinking about it, not sure Union will work.
because I think that these cells will all be merged into 1 cell

"B23:B24,B25:B26,B27:B28,B29:B30

So the whole purpose of me doing this is that I have a number of array formulas (confirmed with CSE) in the cells that are linked to external data sources. The cells are merged.

Via VBA I am going to unmerge them, update/change the links to the external sources, then re-merge them.

I can't update the external links on arrays in merged cells. I have tried and it doesn't work unless the cells are un-merged first ..... unless I am doing it wrong and I can in fact update the links in the arrays in the merged cells, without having to un-merge them first.

-Spydey

P.S. I am going to give Union a try and see what I can do with it. Any other thoughts on it this would be helpful. Thanks everyone!

 

[spydey]

Yes it would, sorry, I didn't read your original post properly, I have now deleted my response.
Apologies for any confusion !

Hey, no worries man. I appreciate the contribution and the effort. Do you have any other thoughts or ideas that might help with what I am trying to achieve?

Thanks!! :D :D

-Spydey