Select and Copy multiple ranges with VBA

Issue

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

Solution

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.

Answered By – Shai Rado

Answer Checked By – Mildred Charles (AngularFixing Admin)

Leave a Reply

Your email address will not be published.