This is a lot of words to explain a very simple question:

Why is VBA upset about so few areas in a range variable?

When I try to do the union of two range variables, the result only has some of the second variable appended to the first.

Clearly I’m hitting a size limit, but darned if I can find it.

======================================

I am using Excel 2405 (Build 17628.20188) in Office 365.

Test results are here, and code is attached. Basically “Set Rx3 = Union(Rx1, Rx2)”, x =[1,2,3,4]

Union works fine with small test ranges like R11 and R12, with 3 areas each. R13 correctly equals all 6 areas:

Set R11 = ws.Range("$B$49:$C$49,$C$50:$C$53,$B$54:$C$54")
Set R12 = ws.Range("$A$55:$C$55,$C$56:$C$59,$B$60:$C$60")
Set R13 = Union(R11, R12)
R13:
$B$49:$C$49,$C$50:$C$53,$B$54:$C$54,$A$55:$C$55,$C$56:$C$59,$B$60:$C$60

======================================

But with the ranges I care about (R21 and R22), the result only adds the first one area of R22 ($A$55:$C$55)
onto the end of R21 (w/o error message):

Set R21 = ws.Range("$A$4:$C$4,$C$5:$C$8,$B$9:$C$9,$C$10:$C$13,$B$14:$C$14,$C$15:$C$18,$B$19:$C$19,$C$20:$C$23, _
$B$24:$C$24,$C$25:$C$28,$B$29:$C$29,$C$30:$C$33,$B$34:$C$34,$C$35:$C$38,$B$39:$C$39,$C$40:$C$43, _
$B$44:$C$44,$C$45:$C$48,$B$49:$C$49,$C$50:$C$53,$B$54:$C$54")
Set R22 = ws.Range("$A$55:$C$55,$C$56:$C$59,$B$60:$C$60,$C$61:$C$64,$B$65:$C$65,$C$66:$C$69,$B$70:$C$70,$C$71:$C$74, _
$B$75:$C$75,$C$76:$C$79,$B$80:$C$80,$C$81:$C$84,$B$85:$C$85,$C$86:$C$89,$B$90:$C$90,$C$91:$C$94, _
$B$95:$C$95,$C$96:$C$99,$B$100:$C$100,$C$101:$C$104")
Set R23 = Union(R21, R22)
R23: 
$A$4:$C$4,$C$5:$C$8,$B$9:$C$9,$C$10:$C$13,$B$14:$C$14,$C$15:$C$18,$B$19:$C$19,$C$20:$C$23,$B$24:$C$24, _
$C$25:$C$28,$B$29:$C$29,$C$30:$C$33,$B$34:$C$34,$C$35:$C$38,$B$39:$C$39,$C$40:$C$43,$B$44:$C$44,$C$45:$C$48, _
$B$49:$C$49,$C$50:$C$53,$B$54:$C$54,$A$55:$C$55

======================================

With R31 = R21 w/o the last area $B$54:$C$54, now R33 has the first two areas from R32 = R22 appended to R31,
$A$55:$C$55,$C$56:$C$59:

Set R31 = ws.Range("$A$4:$C$4,$C$5:$C$8,$B$9:$C$9,$C$10:$C$13,$B$14:$C$14,$C$15:$C$18,$B$19:$C$19,$C$20:$C$23, _
$B$24:$C$24,$C$25:$C$28,$B$29:$C$29,$C$30:$C$33,$B$34:$C$34,$C$35:$C$38,$B$39:$C$39,$C$40:$C$43,$B$44:$C$44, _
$C$45:$C$48,$B$49:$C$49,$C$50:$C$53")
Set R32 = ws.Range("$A$55:$C$55,$C$56:$C$59,$B$60:$C$60,$C$61:$C$64,$B$65:$C$65,$C$66:$C$69,$B$70:$C$70,$C$71:$C$74, _
$B$75:$C$75,$C$76:$C$79,$B$80:$C$80,$C$81:$C$84,$B$85:$C$85,$C$86:$C$89,$B$90:$C$90,$C$91:$C$94,$B$95:$C$95, _
$C$96:$C$99,$B$100:$C$100,$C$101:$C$104")
Set R33 = Union(R31, R32)
R33: 
$A$4:$C$4,$C$5:$C$8,$B$9:$C$9,$C$10:$C$13,$B$14:$C$14,$C$15:$C$18,$B$19:$C$19,$C$20:$C$23,$B$24:$C$24, _
$C$25:$C$28,$B$29:$C$29,$C$30:$C$33,$B$34:$C$34,$C$35:$C$38,$B$39:$C$39,$C$40:$C$43,$B$44:$C$44,$C$45:$C$48, _
$B$49:$C$49,$C$50:$C$53,$A$55:$C$55,$C$56:$C$59

======================================

And lastly, when R41 is the cut-and-paste union of R21 and R22, VBA throws and error:

Set R41 = Range("$A$4:$C$4,$C$5:$C$8,$B$9:$C$9,$C$10:$C$13,$B$14:$C$14,$C$15:$C$18,$B$19:$C$19,$C$20:$C$23, _
$B$24:$C$24,$C$25:$C$28,$B$29:$C$29,$C$30:$C$33,$B$34:$C$34,$C$35:$C$38,$B$39:$C$39,$C$40:$C$43,$B$44:$C$44, _
$C$45:$C$48,$B$49:$C$49,$C$50:$C$53,$B$54:$C$54,$A$55:$C$55,$C$56:$C$59,$B$60:$C$60,$C$61:$C$64,$B$65:$C$65, _
$C$66:$C$69,$B$70:$C$70,$C$71:$C$74,$B$75:$C$75,$C$76:$C$79,$B$80:$C$80,$C$81:$C$84,$B$85:$C$85,$C$86:$C$89, _
$B$90:$C$90,$C$91:$C$94,$B$95:$C$95,$C$96:$C$99,$B$100:$C$100,$C$101:$C$104")

“Run-time error ‘1004’: Method ‘Range’ of object ‘_Global’ failed

======================================

Any advice would be greatly appreciated!

======================================

```
Sub Foo()

Dim ws As Worksheet
Set ws = Worksheets("DATA")
ws.Activate

Dim R11, R12, R13, R21, R22, R23, R31, R32, R33, R41 As Range

Set R11 = ws.Range("$B$49:$C$49,$C$50:$C$53,$B$54:$C$54")
Set R12 = ws.Range("$A$55:$C$55,$C$56:$C$59,$B$60:$C$60")
Set R13 = Union(R11, R12)

MsgBox "R11: " & vbNewLine & R11.Address
MsgBox "R12: " & vbNewLine & R12.Address
MsgBox "R13: " & vbNewLine & R13.Address

Set R21 = ws.Range("$A$4:$C$4,$C$5:$C$8,$B$9:$C$9,$C$10:$C$13,$B$14:$C$14,$C$15:$C$18,$B$19:$C$19,$C$20:$C$23, _
$B$24:$C$24,$C$25:$C$28,$B$29:$C$29,$C$30:$C$33,$B$34:$C$34,$C$35:$C$38,$B$39:$C$39,$C$40:$C$43, _
$B$44:$C$44,$C$45:$C$48,$B$49:$C$49,$C$50:$C$53,$B$54:$C$54")
Set R22 = ws.Range("$A$55:$C$55,$C$56:$C$59,$B$60:$C$60,$C$61:$C$64,$B$65:$C$65,$C$66:$C$69,$B$70:$C$70,$C$71:$C$74, _
$B$75:$C$75,$C$76:$C$79,$B$80:$C$80,$C$81:$C$84,$B$85:$C$85,$C$86:$C$89,$B$90:$C$90,$C$91:$C$94, _
$B$95:$C$95,$C$96:$C$99,$B$100:$C$100,$C$101:$C$104")
Set R23 = Union(R21, R22)

MsgBox "R21: " & vbNewLine & R21.Address
MsgBox "R22: " & vbNewLine & R22.Address
MsgBox "R23: " & vbNewLine & R23.Address

Set R31 = ws.Range("$A$4:$C$4,$C$5:$C$8,$B$9:$C$9,$C$10:$C$13,$B$14:$C$14,$C$15:$C$18,$B$19:$C$19,$C$20:$C$23, _
$B$24:$C$24,$C$25:$C$28,$B$29:$C$29,$C$30:$C$33,$B$34:$C$34,$C$35:$C$38,$B$39:$C$39,$C$40:$C$43,$B$44:$C$44, _
$C$45:$C$48,$B$49:$C$49,$C$50:$C$53")
Set R32 = ws.Range("$A$55:$C$55,$C$56:$C$59,$B$60:$C$60,$C$61:$C$64,$B$65:$C$65,$C$66:$C$69,$B$70:$C$70,$C$71:$C$74, _
$B$75:$C$75,$C$76:$C$79,$B$80:$C$80,$C$81:$C$84,$B$85:$C$85,$C$86:$C$89,$B$90:$C$90,$C$91:$C$94,$B$95:$C$95, _
$C$96:$C$99,$B$100:$C$100,$C$101:$C$104")
Set R33 = Union(R31, R32)

MsgBox "R31: " & vbNewLine & R31.Address
MsgBox "R32: " & vbNewLine & R32.Address
MsgBox "R33: " & vbNewLine & R33.Address

Set R41 = ws.Range("$A$4:$C$4,$C$5:$C$8,$B$9:$C$9,$C$10:$C$13,$B$14:$C$14,$C$15:$C$18,$B$19:$C$19,$C$20:$C$23, _
$B$24:$C$24,$C$25:$C$28,$B$29:$C$29,$C$30:$C$33,$B$34:$C$34,$C$35:$C$38,$B$39:$C$39,$C$40:$C$43, _
$B$44:$C$44,$C$45:$C$48,$B$49:$C$49,$C$50:$C$53,$B$54:$C$54,$A$55:$C$55,$C$56:$C$59,$B$60:$C$60, _
$C$61:$C$64,$B$65:$C$65,$C$66:$C$69,$B$70:$C$70,$C$71:$C$74,$B$75:$C$75,$C$76:$C$79,$B$80:$C$80, _
$C$81:$C$84,$B$85:$C$85,$C$86:$C$89,$B$90:$C$90,$C$91:$C$94,$B$95:$C$95,$C$96:$C$99,$B$100:$C$100, _
$C$101:$C$104")

End Sub
```