How to copy non contiguous cell ranges

1. Introduction

What is a non contiguous cell range?

A non-contiguous cell range in Excel is a selection of multiple cells that are not next to each other, like "A1, C3, and E5".

What is a macro?

Macros are sequences of instructions or actions that can be recorded and replayed in Excel to automate repetitive tasks.

• Automate tasks such as formatting, calculations, data manipulation, or interacting with other workbooks.
• You can record macros using the built-in Excel Macro Recorder, which generates VBA (Visual Basic for Applications) code for the actions you perform in Excel.
• Alternatively, you can write macros manually in the VBA editor for more control and flexibility.
• Macros can be run from the Developer tab, assigned to a button, or triggered through custom VBA code.

What is VBA?

VBA (Visual Basic for Applications) is a programming language developed by Microsoft that is used for automating tasks in Microsoft Office applications such as Excel, Word, and Access. It allows users to create macros, automate repetitive tasks, and enhance Office functionality with custom scripts.

What is the "Developer" tab?

The Developer tab is an advanced feature in Microsoft Excel that provides tools for automation, coding, and customization. It includes:

• VBA (Visual Basic for Applications): Write, record and edit macros.
• ActiveX Controls & Form Controls: Create buttons, check boxes, and interactive elements.
• XML Tools: Work with XML maps and data.
• Add-ins Management: Enable and configure Excel Add-ins.

How to enable the "Developer tab"?

Here is how to enable the "Developer" tab if it is missing on the ribbon:

1. Press with left mouse button on "File" located above the ribbon, a new pane appears.
2. Press with mouse on the "Options" button to access Excel settings.
3. Press with mouse on "Customize Ribbon" and then on the right side press with left mouse button on the checkbox next to the tab "Developer" to enable it.
4. Press with left mouse button on "OK" button to apply changes.

The "Developer" tab is now visible on the ribbon. Here are instructions for Excel 2007, Excel 2010 and Excel 2013

What is a dynamic cell range?

A dynamic cell range automatically expands or shrinks when you add or remove data.

2. 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 non-contiguous cell ranges that don't share the same column letters or row numbers?
The following macro allows you to do that.

3. 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.

3.1 VBA Code

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

3.2 Where to put the code?

1. Open the Visual Basic Editor (Alt + F11).
2. Press with left mouse button on "Insert" on the top menu bar.
3. Press with left mouse button on "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.

3.3 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. Press with left mouse button on "Run" button
5. A dialog box appears asking you for the destination cell.
   

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

3.4 Get Excel *.xlsm file

How to copy non contiguous cell ranges.xlsm

4. How to copy every n-th row from a list

This article demonstrates a formula and a VBA macro that returns every n-th row from a given cell range. The image above shows a list in cell range B2:F12 and a formula in cell range H3:L7 extracting every other row from cell range B3:F12.

The following link takes you to an article that explains how to copy rows matching a condition: Extract multiple records based on a condition

Formula in cell H3:

The SEQUENCE function was introduced in January 2020 and is available to Excel 365 subscribers. It generates a series of numbers based on the rows, columns, start and step arguments. The columns, start and step arguments are optional.

SEQUENCE(rows,[columns],[start],[step])

I will now describe another formula below for older Excel versions.

Formula in cell H3:

This formula is smaller, however, you need to copy cell H3 and paste to adjacent cells as far as needed both horizontally and vertically. This is not necessary with the first formula above as it expands automatically, this behavior by the formula is called spilling and is a feature for Excel 365 subscribers.

4.1 Explaining formula for older versions in cell H3

The steps below describe how to get every other row, however, I will also explain below what to change in order to get every nth row.

Step 1 - INDEX function

The INDEX function lets you fetch a given value, it requires a row number and a column number in order to return a specific value from a given cell range.

The row and column arguments allow you to specify which value to get from the cell range.

INDEX(cell_ref, [row], [col])

Step 2 - Calculate row number

To get every nth row we need to create a formula that returns a number series based on a step value. The ROWS function returns a number which represents the number of rows a cell range contains.

The cell reference $A$1:A1 expands when you copy cell H3 and paste to cells below, the first part is an absolute cell reference $A$1 which means it is locked to cell A1.

The second part A1 is a relative cell reference. It changes when the cell is copied to cells below as far as needed.

ROWS($A$1:A1)*2-1

becomes

1*2-1

and returns 1.

Step 3 - Calculate column number

The COLUMNs function returns the number of columns in a cell range. The arumnet contains a cell range that expands as well when the cell is copied to cells below.

COLUMNS($A$1:A1))

returns 1.

Step 4 - Return value

INDEX($B$3:$F$12,ROWS($A$1:A1)*2-1,COLUMNS($A$1:A1))

becomes

INDEX($B$3:$F$12,1,1)

becomes

$B$3

and returns 1 in cell H3.

4.2 What to change in formulas if I want every nth row?

Excel 365 formula:

The first argument in the SEQUENCE function specifies the number of values the number series will contain. The last argument is the step value to use.

SEQUENCE(5, , , 2)

returns {1; 3; 5; 7; 9}

If you want every fifth row and a total of twenty rows then the SEQUENCE function becomes:

SEQUENCE(20, , , 5)

returns {1; 6; 11; 16; 21; 26; 31; 36; 41; 46; 51; 56; 61; 66; 71; 76; 81; 86; 91; 96}

You can also specify which number to start with.

SEQUENCE(20, ,2 , 5)

returns

{2; 7; 12; 17; 22; 27; 32; 37; 42; 47; 52; 57; 62; 67; 72; 77; 82; 87; 92; 97}

Formula for all Excel versions:

You want to change the bolded numbers in the above formula to get every nth value.

n-(n-1)

Example 1, to get every fourth number change 2-1 to 4-3.

Example 2, to fetch every 7th row change 2-1 to 7-6.

5. Copy every nth row - VBA macro

The image above shows the macro displaying an inputbox asking the Excel user for a step value.

Sub CopyNthRow()

'Ask for step number
sn = CInt(Application.InputBox("Enter step number", , , , , , , 1))

'Row number to start with
st = CInt(Application.InputBox("Enter starting row number", , , , , , , 1))

'Ask for cell range
Set Rng = Application.InputBox(Prompt:="Select cell range:", _
Title:="Copy every nth rows", _
Default:=Selection.Address, Type:=8)

'Create new worksheet
Set Ws = Sheets.Add

'Save number 1 to variable r
r = 1

'Save number of rows in cell range
co = CInt(Rng.Rows.CountLarge)

'Copy rows based on step number
For i = st To CInt(co) Step sn

    'Paste to new worksheet
    Rng.Rows(i).Copy Ws.Range("A" & r)

    'Add 1 to r
    r = r + 1

'Continue with next number based on variable i
Next i

End Sub

5.1 Where to put the code?

1. Press Alt + F11 to open the Visual Basic Editor.
2. Press with left mouse button on "Insert" on the top menu, see image above.
3. Press with left mouse button on "Module". A module named Module1 appears in the Project Explorer, see image above.
4. Paste code to window.
5. Return to Excel.

5.2 How to run macro?

1. Press Alt + F8 to open the Macro dialog box.
2. Press with mouse on the macro name, in this case CopyNthRow.
3. Press with left mouse button on "Run" button.

5.3 What happens when you run the macro?

The macro asks the user for a step number (n), a start row number, and a cell range to use. It will now copy every nth row from the supplied cell range to a new worksheet.

6. Copy a dynamic cell range - VBA

In this blog post, I will demonstrate some VBA copying techniques that may be useful if you don't know the exact size of the cell range.

I will also at the very end explain a better solution that is far more easy to programmatically manipulate than a regular cell range.

Table of Contents

1. Copy cell range
2. Copy a dynamic cell range
3. Copy a dynamic cell range containing blank
4. Copy a dynamic cell range containing blank rows and blank cells
5. Excel defined Table

Let's start with a basic example.

6.1 How do I copy a cell range using VBA?

This macro copies cell range A1:B2 to A5:B6 in the active sheet. You can make the code even shorter than shown in the image above: Range("A1:B2").Copy Range("A5").

This technique is not dynamic because the ranges are hardcoded in the macro. It will also not only copy values but also cell formatting.

'Name macro
Sub CopyRange()

'Copy cell range A1:B2 and paste to cell A5
Range("A1:B2").Copy Range("A5")

End Sub

The following macro pastes only values not cell formatting.

'Name macro
Sub CopyRange()

'Copy cell range A1:B2
Range("A1:B2").Copy

'Paste values to cell A5
Range("A5").PasteSpecial xlPasteValues

End Sub

Microsoft Excel reference: Worksheet Range Property

Back to top

7. How do I copy a dynamic cell range using VBA?

A dynamic cell range is a cell range that may change from time to time, you don't know exactly the size.

The current region is a cell range surrounded by blank rows and columns, in other words, non-contiguous or non-adjacent cell ranges

This is equivalent to the shortcut keys Ctrl + A. This works very well if the cell range doesn't have blank rows or columns.

'Name macro
Sub CopyRange1()

'Use cell A1 to copy it and all adjacent values to cell A5
Range("A1").CurrentRegion.Copy Range("A5")

End Sub

Microsoft Excel reference: Range.CurrentRegion Property

Back to top

7.1 How do I copy a dynamic cell range containing blank rows using VBA?

The image above shows a blank row making the cell range non-contiguous or non-adjacent. In order to get the entire range Excel starts at the last cell in column A and looks for the first non-empty value upwards.

The row number is then used to build the cell range you want to copy.

'Name macro
Sub CopyRange2()

'Save row number of last non-empty cell to variable Lrow
Lrow = Range("A" & Rows.Count).End(xlUp).Row

'Copy cell range A1 to last row of non-empty cell and append it to column B then paste to cell A9
Range("A1:B" & Lrow).Copy Range("A9")

End Sub

Microsoft Excel reference: End Property 

Microsoft Excel reference: Worksheet Rows Property

Back to top

7.2 How do I copy a dynamic cell range containing blank rows and blank cells using VBA?

This example demonstrates a scenario where a cell range (A1:B6) has a blank row (4) and a blank cell (A6), you have to check both columns in order to identify the row of the last non-empty value.

The MAX function returns the maximum row number from these two columns.

'Name macro
Sub CopyRange3()

'Save largest number of last non-empty row based on column A and B to variable Lrow
Lrow = Application.Max( _
Range("A" & Rows.Count).End(xlUp).Row, _
Range("B" & Rows.Count).End(xlUp).Row)

'Use variable Lrow to build a cell reference then copy cell range and paste to cell A9
Range("A1:B" & Lrow).Copy Range("A9")

End Sub

Microsoft Excel reference: End Property 

Back to top

7.3 Excel defined Table

I highly recommend you use an Excel defined Table, it is dynamic by design. This way you will save a lot of time and effort to create code that will handle all possible scenarios.

'Name macro
Sub CopyTable()

'Copy Excel defined Table Table1 located active worksheet
Range("Table1").Copy

'Paste only values to cell B7
Range("B7").PasteSpecial xlPasteValues

End Sub

The above macro copies Table1 and pastes only values and not formatting to cell B7, you can see the result in the image above.

Back to top

Macro category

Vba category

More than 1300 Excel formulas

Excel categories

Comments (2)