Copying and Pasting Non-Contiguous Ranges of Cells

Szilvia can create a selection set of cells, such that she has a non-contiguous range of cells selected. If she tries to copy those cells using Ctrl+C, Excel informs her that "this action will not work on multiple selections." Szilvia wonders if there a way to copy a non-contiguous range and then paste that range so that the cells are in the same relative position as in the original selection.

Apparently, this limitation of only allowing you to copy and paste contiguous ranges is the way that Excel is designed to work. The only way you can copy and paste a non-contiguous range is to use a macro—one that steps through each area in a selection and pastes it relative to a target address.

There is one exception to this limitation, however. If your non-contiguous selection consists of ranges that all come from the same rows, you can copy and paste them using the standard Ctrl+C and Ctrl+V commands. For example, if you select the range A13:D16, then hold Ctrl and select F13:G16, you can copy and paste these ranges. The pasted data will appear in adjacent columns in the destination. This works because the rows are aligned across all selected areas.

For all other cases where the non-contiguous ranges are not row-aligned, you will need to use a macro. The following macro provides a robust solution:

Sub CopyPasteCells()
    Dim Area As Range
    Dim PasteRng As Range
    Dim C As Integer
    Dim R As Double
    Dim X As Integer
    Dim Y As Double

    On Error Resume Next
    Set PasteRng = Application.InputBox("Target cell?", Type:=8)
    If PasteRng Is Nothing Then Exit Sub
    On Error GoTo 0

    C = Selection.Column
    R = Selection.Row
    For Each Area In Selection.Areas
        X = Area.Column
        If X < C Then C = X
        Y = Area.Row
        If Y < R Then R = Y
    Next Area
    
    C = PasteRng.Column - C
    R = PasteRng.Row - R
    For Each Area In Selection.Areas
        Area.Copy Cells(Area.Row + R, Area.Column + C)
    Next Area
    Application.CutCopyMode = False
End Sub

This macro works by using VBA's Areas collection to copy each contiguous block of the selection at a time, which is faster than copying cell by cell. It correctly calculates the relative position of each area and pastes it in the proper location relative to the target cell you specify.

To use the macro, create your selection set of cells (holding Ctrl while clicking to select non-contiguous ranges). Then, run the macro and specify the target cell for the pasting. Excel displays an input box where you can either type the cell address or click directly on the target cell. The cells are pasted relative to that target cell on the current worksheet, maintaining their original spacing and positions.

If you need to cut and paste (rather than copy and paste), you can modify the macro by changing Area.Copy to Area.Cut in the second For Each loop.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5228) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.