Limit to the number of rows Excel VBA will select/copy

[jw116]

I'm trying to make a macro that will select the even number of rows and paste into a new workbook.
-------------------------------------------
Here is my code:


Sub rows()
Dim s, collection As String
collection = "2:2"
endrow = 44
'errors when I put anything above 44

For x = 2 To endrow
s = 2 * x & ":" & 2 * x
collection = collection & "," & s
Range(collection).Copy
Next x

End Sub

------------------------


The code stops working when I change endrow from 44 to 45. There appears to be a limit to the number of rows Excel VBA wants to copy or select. The only other way I can think of is making a loop that selects one row and immediately pastes it into the new workbook and repeats. Is there any other way that it can select all the rows that fit the criteria and pastes it over in one block?



Any help would be appreciated!

 

[Norie]

Try this.

Sub CopyEvenRows()
Dim s As Long
Dim endrow As Long
Dim x As Long

Dim collection As Range

    Set collection = Range("2:2")
    endrow = 45
    'errors when I put anything above 44

    For x = 2 To endrow

        s = 2 * x & ":" & 2 * x

        Set collection = Union(collection, Range(s))

    Next x
    
    collection.Copy
    
End Sub