excel vba error 1004 Range

Question

So i've got 2 Range() items:

Set myRange1 = Range("E2:E8,D9:D12,F9:F12,J2:J8,I9:I12,K9:K12,O2:O8,N9:N12,P9:P12,E14:E20,D21:D24,F21:F24,J14:J20,I21:I24,K21:K24,O14:O20,N21:N24,P21:P24,E26:E32,D33:D36,F33:F36,J26:J32,I33:I36,K33:K36,O26:O32,N33:N36,P33:P36")
Set myRange2 = Range("C6:C9,E7:E9,G7:G9,J6:J9,L7:L9,N7:N9,Q6:Q9,S7:S9,U7:U9,X6:X9,Z7:Z9,AB7:AB9,C12:C15,E13:E15,G13:G15,J12:J15,L13:L15,N13:N15,Q12:Q15,S13:S15,U13:U15,X12:X15,Z13:Z15,AB13:AB15,C18:C21,E19:E21,G19:G21,J18:J21,L19:L21,N19:N21,Q18:Q21,S19:S21,U19:U21,X18:X21,Z19:Z21,AB19:AB21")

myRange1 is fine, myRange2 throws Run-time Error 1004 Method 'Range' of Object '_Global' Failed.

Does anyone know why? Is there too many ranges in a range? In my opinion they're both pretty long.

Not too many addresses, but too many characters. i.e. it is the length of the string being passed to the Range method that is the issue - it has a maximum length of 255 characters. — YowE3K
– YowE3K, Commented Jun 8, 2017 at 9:50

Vityata · Accepted Answer · 2017-06-08 10:50:04Z

3

Too many is the correct answer. However, if you want to use them try this trick:

Set myRange2 = Range("C6:C9,E7:E9,G7:G9,J6:J9,L7:L9,N7:N9,Q6:Q9,S7:S9,U7:U9,X6:X9,Z7:Z9,AB7:AB9")
Set myRange2 = Union(myRange2,range("C12:C15,E13:E15,G13:G15,J12:J15,L13:L15,N13:N15,Q12:Q15,S13:S15,U13:U15,X12:X15,Z13:Z15,AB13:AB15,C18:C21,E19:E21,G19:G21,J18:J21,L19:L21,N19:N21,Q18:Q21,S19:S21,U19:U21,X18:X21,Z19:Z21,AB19:AB21"))

You can have up to 30 arguments in a Union:

https://msdn.microsoft.com/en-us/library/office/ff834621.aspx

But if you increment like in the code above, you can go like this forever.

edited Jun 8, 2017 at 10:50

answered Jun 8, 2017 at 9:39

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

excel vba error 1004 Range

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related