How to copy non contiguous cell ranges
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?
- Open the Visual Basic Editor (Alt + F11).
- Press with left mouse button on "Insert" on the top menu bar.
- Press with left mouse button on "Module" to insert a code module to your workbook. Module1 automatically appears in the VB Project window.
- Paste the code into the code module.
- Save your workbook as a macro-enabled workbook, the file extension looks like this: *.xlsm
- Exit Visual Basic Editor.
How to use the macro
- You simply hold CTRL key while selecting the cell ranges.
- Then press Alt+F8 to see a list of macros.
- Select CopySelections.
- Press with left mouse button on "Run" button
- A dialog box appears asking you for the destination cell.
That is it, the selections are copied to your destination cell.
Get Excel *.xlsm file
Macro category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]
Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
Today I would like to share with you these small event handler procedures that make it easier for you to […]
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]
This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]
Vba category
Today I'll show you how to search all Excel workbooks with file extensions xls, xlsx and xlsm in a given folder for a […]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
The macro demonstrated above creates hyperlinks to all worksheets in the current worksheet. You will then be able to quickly […]
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
This article demonstrates a macro that copies selected rows based on enabled check boxes. The image above shows data on […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
This user defined function creates a unique distinct list of words and how many times they occur in the selected […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]
This article demonstrates a macro that allows you to search for a text string in multiple worksheets and workbooks located […]
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
Excel categories
2 Responses to “How to copy non contiguous cell ranges”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Hi Oscar,
VBA is new to me and I was looking for a code to copy non contiguous cells in one row on another tab and this seems to do the job very well. However I still have two issues which I have troubles to solve:
- can it be that if the amount of cells I try to select is too large, I get a stack overflow? is there a way to select the first half and to 'hold on' and select the second half?
- some of the cells that i want to copy contain summations of other cells, when copied the formula is shown instead of the number. Where in the code should I add the paste special?
Thanks a lot for taking the time to review this question!
VBA dummie,
- can it be that if the amount of cells I try to select is too large, I get a stack overflow? is there a way to select the first half and to 'hold on' and select the second half?
I am guessing here, perhaps the macro is repeating forever? Are you using event code?
- some of the cells that i want to copy contain summations of other cells, when copied the formula is shown instead of the number. Where in the code should I add the paste special?
This article shows you how to copy values and not cell formulas:
https://www.get-digital-help.com/2012/01/24/copy-a-cell-range-whose-size-is-likely-to-change-from-time-to-time-vba/