Select and Copy multiple ranges with VBA

Question

I want to copy multiple range to another workbook. I have the code below. How can I replace the number 1000 by iLastRow

iLastRow = Sh.Range("B" & Rows.Count).End(xlUp).Row
sh.Range("A3:AG1000, AL3:EJ1000").Select
Selection.Copy

Answer 1

Try the code below, explanation inside the code as comments:

Option Explicit

Sub CopyMultipleRanges()

Dim iLastRow As Long
Dim sh As Worksheet
Dim MultiRng As Range

Set sh = ThisWorkbook.Worksheets("Sheet1") ' <-- change to your sheet's name
With sh
    iLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

    ' use the union to set a range combined from multiple ranges
    Set MultiRng = Union(.Range("A3:A" & iLastRow), .Range("AL3:EJ" & iLastRow))
End With

' copy the range, there's no need to select it first
MultiRng.Copy

End Sub

Another question is how you want to paste the merged reanges that have a gap in the middle.

Answer 2

The Union method is a solution to this problem. but it also has its cons

The union range should be the same first row and last row. On the other hand, you can just select the first cell to paste. you can alway do like this. the main point here is the row number should be the same. here I synchronize both range with the same variable. in your case, change to last cell.

j=1
i = 4
Set MultiRng = Union(Range("A" & j & ":B" & i), Range("D" & j & ":E" & i))