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.
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.
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.
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
Where to put the code?
- Press Alt + F11 to open the Visual Basic Editor.
- Press with left mouse button on "Insert" on the top menu, see image above.
- Press with left mouse button on "Module". A module named Module1 appears in the Project Explorer, see image above.
- Paste code to window.
- Return to Excel.
How to run macro?
- Press Alt + F8 to open the Macro dialog box.
- Press with mouse on the macro name, in this case CopyNthRow.
- Press with left mouse button on "Run" button.
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.
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 workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
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 […]
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 […]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]
Today I would like to share with you these small event handler procedures that make it easier for you to […]
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 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 […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
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 […]
Functions in this article
More than 600 Excel formulas
Excel categories
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.