Is there a limit on the size of a range?

[ouadad]

Simple code. I just want it to select a lot of rows and copy them, but I get a "Method Range of Object Global Fail".

Sub Copy_h1()
'
        Range( _
        "83:83,86:86,89:89,106:106,122:122,139:139,155:155,172:172,188:188,204:204,260:260,263:263,266:266,283:283,299:299,316:316,332:332,349:349,365:365,381:381,437:437,440:440,443:443,460:460,476:476,493:493,509:509,526:526,542:542,558:558,615:615,618:618,621:621,638:638,654:654,671:671,687:687,704:704,720:720,736:736" _
        ).Select
    Selection.Copy
End Sub

 

[hotabae]

There is a 255 character limit when you feed a range as a string.

83:83,86:86,89:89,106:106,122:122,139:139,155:155,172:172,188:188,204:204,260:260,263:263,266:266,283:283,299:299,316:316,332:332,349:349,365:365,381:381,437:437,440:440,443:443,460:460,476:476,493:493,509:509,526:526,542:542,558:558,615:615,618:618,621:621,638:638,654:654,671:671,687:687,704:704,720:720,736:736

 

[Peter_SSs]

Make a number of smaller ranges, say
Set RangeA = ...
Set RangeB = ...
Set RangeC = ...
then you can combine them into one. For example Union(RangeA, RangeB, RangeC).Copy

 

[Joe4]

You can make two smaller ranges and combine them using UNION, i.e.

    Dim rng1 As Range, rng2 As Range, rng3 As Range
    
    Set rng1 = Range("83:83,86:86,89:89,106:106,122:122,139:139,155:155,172:172,188:188,204:204,260:260,263:263,266:266,283:283,299:299,316:316,332:332")
    Set rng2 = Range("349:349,365:365,381:381,437:437,440:440,443:443,460:460,476:476,493:493,509:509,526:526,542:542,558:558,615:615,618:618,621:621,638:638,654:654,671:671,687:687,704:704,720:720,736:736")
    Set rng3 = Union(rng1, rng2)
    
    rng3.Select

 

[ouadad]

Thank you!

 

[My Aswer Is This]

What is your criteria on what rows get copied?
And where do you plan to paste them.

Let the script do all the work so you do not need to enter all those rows into your script.

 

[Peter_SSs]

Thank you!

You are welcome.

If you do have a fixed set of rows to be copied, here is another way that you could consider that can deal with a large number of rows and not only saves setting up the sub-ranges but also saves you having to repeat each row number with the colon between.
Also note that ranges generally do not need to be 'selected' to work with them and selecting slows your code.

Sub Copy_h1_v2()
  Dim Rng As Range
  Dim aRws As Variant
  Dim i As Long
  
  Const Rws As String = "83,86,89,106,122,139,155,172,188,204,260,263,266,283,299,316,332,349,365,381,437" _
                        & ",440,443,460,476,493,509,526,542,558,615,618,621,638,654,671,687,704,720,736"
  
  aRws = Split(Rws, ",")
  Set Rng = Rows(aRws(0))
  For i = 1 To UBound(aRws)
    Set Rng = Union(Rng, Rows(aRws(i)))
  Next i
  Rng.Copy
End Sub