Author: Oscar Cronquist Article last updated on June 12, 2018

If you try to copy multiple cell ranges on a worksheet that don't have the same number of rows or columns you get the following error message.

This action won't work on multiple selections, see picture above. What? The cell ranges above have the exact same size?

Yes, however, they also need to be on the same columns or rows.

The image to the right shows selections that share the same column letters, in this example column B and C.

If the selected cell ranges share the same column letters your selected cell ranges may have a different number of rows.

What do you mean? The following picture shows multiple selections that share the same column letters but each selection has a different number of rows.

The picture above shows three selections, the first selection contains one row, the second contains two rows and the third selection has 3 rows.

The same thing applies to cell ranges that share the same row numbers.

To sum it up, remember that the selections must share the same column letters or row numbers to be able to copy non contiguous cell ranges.

I need to copy multiple noncontiguous cell ranges that don't share the same column letters or row numbers? The following macro allows you to do that.

Copy non-contiguous cell ranges (Macro)

The picture above demonstrates a macro that allows you to copy non contiguous cell ranges. In this example, the selections are copied to destination cell F3.

The macro will show you a dialog box that allows you to select a destination cell. Each selection is then copied to the destination cell or the first empty cell below.

Visual Basic Macro

Sub CopySelections()
Set cellranges = Application.Selection
Set ThisRng = Application.InputBox("Select a destination cell", "Where to paste slections?", Type:=8)
For Each cellrange In cellranges.Areas
    cellrange.Copy ThisRng.Offset(i)
    i = i + cellrange.Rows.CountLarge
Next cellrange
End Sub

Where to put the code?

  1. Open the Visual Basic Editor (Alt + F11).
  2. Click "Insert" on the top menu bar.
  3. Click "Module" to insert a code module to your workbook. Module1 automatically appears in the VB Project window.
  4. Paste the code into the code module.
  5. Save your workbook as a macro-enabled workbook, the file extension looks like this: *.xlsm
  6. Exit Visual Basic Editor.

How to use the macro

  1. You simply hold CTRL key while selecting the cell ranges.
  2. Then press Alt+F8 to see a list of macros.
  3. Select CopySelections.
  4. Click "Run" button
  5. A dialog box appears asking you for the destination cell.

That is it, the selections are copied to your destination cell.

Download Excel *.xlsm file

How to copy non contiguous cell ranges.xlsm

Related articles

Copy data from workbooks in folder and subfolders

I will in this article demonstrate a macro that automatically opens all workbooks in a folder and subfolders, one by [โ€ฆ]