VBA Select Multiple Ranges to Copy and Paste

In Excel VBA, combine nonadjacent ranges with Application.Union when you need to select them, but copy each range to its destination separately for the most reliable result. A multi-area selection is useful for highlighting or processing cells together; it does not always behave like one rectangular block when you copy and paste.

What a multiple-range selection means

A range can be one continuous block, such as A2:A10, or multiple separate areas, such as A2:A4 and C2:C4. Excel calls the latter a multiple-area, or noncontiguous, range. In VBA, a Range object can represent both kinds. Its Areas collection contains the separate blocks.

This distinction matters because a rectangular block has a straightforward shape and destination. A selection with gaps does not: Excel must decide how the separate pieces map to the paste location. Depending on the operation and destination, copying a multi-area selection at once can produce an error or a result different from what you intended. For repeatable automation, define the source and destination for each block explicitly.

You do not have to select cells in order to copy them. VBA can refer to a range directly, which avoids depending on the active cell or active worksheet. Use .Select only when showing a selection to a user or when a particular Excel command genuinely requires the selection.

Select nonadjacent ranges with Union

Application.Union combines two or more ranges into one multiple-area Range object. All arguments must refer to ranges on the same worksheet. The following macro selects A2:A4 and C2:C4 on a sheet named Sheet1:

Sub SelectTwoRanges()
    Dim ws As Worksheet
    Dim multiRange As Range

    ' Set the worksheet that contains both ranges.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set multiRange = Application.Union(ws.Range("A2:A4"), ws.Range("C2:C4"))

    ' Select requires the worksheet to be active.
    ws.Activate
    multiRange.Select
End Sub

Change the sheet name and addresses to match your workbook. The activation step is needed here because Excel cannot select a range on a worksheet that is not active. By contrast, code that reads or copies a range directly usually does not need to activate the sheet.

For a quick one-off selection, Excel also accepts a comma-separated address on a worksheet, such as ws.Range("A2:A4,C2:C4"). Union is often clearer in a macro because each range is a separate argument and can be constructed from variables. Do not use Union to combine ranges from different worksheets; handle each worksheet’s range separately.

Copy each area to a matching destination

If you want to preserve formulas, formatting, and other copied cell content, use Range.Copy with a destination for each source block. For example, this sends A2:A4 to E2:E4 and C2:C4 to G2:G4 on the same sheet:

Sub CopyRangesToMatchingLocations()
    Dim ws As Worksheet

    ' Set the worksheet containing the source and destination cells.
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ' Copy each block to its corresponding destination.
    ws.Range("A2:A4").Copy Destination:=ws.Range("E2")
    ws.Range("C2:C4").Copy Destination:=ws.Range("G2")
End Sub

The destination argument can be the top-left cell of the destination block; Excel fills an area matching the source dimensions. This example keeps the two copied blocks separate, just as the source blocks are separate. The destination cells should be clear of data you need to keep, because the copy overwrites cells in the resulting destination areas.

Copying formulas works like an ordinary Excel copy: relative references may adjust to the new location, while absolute references marked with dollar signs remain fixed. If the destination is on another worksheet, qualify it with that worksheet, for example ws.Range("A2:A4").Copy Destination:=targetWs.Range("E2"), after declaring and setting targetWs. The source and destination worksheets do not have to be the same.

Copy values without formulas or formatting

When the goal is to transfer only the current values, direct assignment is often simpler and avoids the Clipboard. The source and destination blocks must have the same dimensions:

Sub CopyValuesOnly()
    Dim ws As Worksheet

    ' Set the worksheet containing both sets of cells.
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ' Transfer values while leaving destination formatting in place.
    ws.Range("E2:E4").Value = ws.Range("A2:A4").Value
    ws.Range("G2:G4").Value = ws.Range("C2:C4").Value
End Sub

These assignments transfer the displayed results stored in the source cells, not their formulas or formatting. For instance, if A2 contains a formula that currently returns 25, the destination receives the value 25. The destination’s existing number format is not copied, so a date or percentage may need formatting separately if the destination does not already have the right format.

For a larger set of cells, this approach can be faster than repeated clipboard operations, but each source and destination block still needs a matching shape. If you want formulas but not formats, or formats but not formulas, use Excel’s paste-special options or assign the specific property you need rather than treating a multi-area selection as one pasteable rectangle.

Choose a destination layout before copying

“Copy multiple ranges” can mean several different things. Decide whether the copied blocks should retain their relative positions, be stacked together, or land in separate locations. These are distinct tasks, and VBA needs a clear destination for each one.

Keep blocks in corresponding positions: pair each source range with its intended destination, as in the E2 and G2 example above.
Stack blocks into one column: copy each source block in turn to a destination row calculated from the previous block’s height. This requires deciding how to order the source areas.
Transfer values only: use matching-size assignments with .Value when formulas and formatting should not move.
Preserve the gaps between source areas: copy each block to a destination that reflects those gaps. Do not expect a multi-area copy to infer the layout you have in mind.

For a one-time manual task, selecting ranges with Ctrl-click and using Excel’s copy and paste commands may be enough. For a macro that must run consistently, explicit source-to-destination pairs make the layout easier to inspect and change later.

Common errors and how to avoid them

“That command cannot be used on multiple selections”: a command may not support the selected areas as a group. Avoid copying the entire union in one operation; copy each area separately to an explicitly chosen destination.
“Select method of Range class failed”: the worksheet containing the range may not be active. Activate it before selecting, or avoid .Select and work with the Range object directly.
Unexpected paste shape: a destination that does not match the source dimensions can cause an error or overwrite unintended cells. Check the number of rows and columns in each source and destination pair.
Data in the destination disappears: copying replaces cells in the destination area. Confirm the target cells are appropriate, or choose an unused area before running the macro.
Formulas change after copying: relative references adjust as they do with a normal worksheet copy. Use absolute references in the original formula when appropriate, or transfer values instead if the destination needs fixed results.
Macro cannot find the sheet: a sheet name in Worksheets("Sheet1") must match the workbook tab name exactly. If the macro belongs to another workbook, check whether ThisWorkbook is the workbook you mean to modify.

Before running a macro that changes cells, save a separate copy of the workbook. Excel macros cannot be undone with Ctrl+Z, and a copy gives you a way to recover if the ranges or destination were set incorrectly. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain the macro.

Manual selection, VBA, and platform notes

In Excel for Microsoft 365 on Windows, you can hold Ctrl and click separate cells or drag separate blocks to create a multiple selection. On Mac, use Command-click to add separate cells or ranges. This is useful for a quick manual action, but it is not a substitute for defining destinations in code when the task must be repeated.

To open the Visual Basic Editor on Windows, press Alt+F11. On many Mac keyboards, the shortcut is Option+F11; some keyboards require Fn+Option+F11. You can also use the Developer tab’s Visual Basic command. If the Developer tab is hidden, enable it in Excel Options on Windows or Excel Settings on Mac. Excel for the web can open workbooks containing macros, but it cannot create, edit, or run VBA macros; use the desktop Excel app for this code.

When you run a macro, review the workbook and macro security settings rather than enabling unknown code. A macro that copies ranges can overwrite cells, and code from an untrusted source may do more than copy data. Keep a backup and run only macros you understand and intend to use.

Frequently Asked Questions

Can I copy a Union range directly to one destination?
Sometimes Excel can copy a multiple-area range, but not all copy and paste operations support it reliably. A single destination block also cannot make the intended placement of separated source blocks obvious. For predictable results, copy each area to a separate destination.

How do I select multiple ranges on different sheets?
You cannot combine ranges from different worksheets in one Application.Union call. Refer to each worksheet separately, and activate a worksheet only if you need to select its range. For copying, use each sheet-qualified Range object directly.

How do I paste multiple selected ranges one below another?
Copy each source area to a destination block beneath the previous one. Set each destination’s starting row based on the number of rows already copied. This makes the stacking order and treatment of blocks explicit; do not rely on a multi-area selection to decide the order for you.

How can I copy only visible cells in several ranges?
Filtered or hidden rows introduce another issue: ordinary range operations may include cells you did not intend to copy. In Excel’s interface, Go To Special can select visible cells only; VBA can use SpecialCells(xlCellTypeVisible), but the resulting selection may still contain multiple areas. Copy those areas individually and check for the case where no matching visible cells are found.

Can I copy ranges without changing the clipboard?
Direct value assignment with .Value does not use the Clipboard and transfers values only. The .Copy Destination:=... method performs a worksheet copy and preserves more cell content, including formulas and formatting, but uses Excel’s copy operation.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.