Return every n-th item using VBA in Microsoft Excel

 

In case you are wondering how someone can find every nth item in a column. In this article we will learn how to return every n-th item from a list. We will use VBA code to find the result.

 

Question: I want a macro code to extract every nth value from a column. I am trying to pull values from one sheet / column into another sheet / column. I tried using many functions like OFFSET but not able to figure out the formula that can give the result.

Let us take an example of 100 values in column A & we want to find out every 10th value in column B

The sample values starting from cell A1 equal to 1 to cell A100 equal to 100; following is the snapshot of data in column A:

 

img1

 

To get result, we need to follow the below steps to launch VB editor

  • Click on Developer tab
  • From Code group, select Visual Basic

 

img2

 

  • Copy the below code in the standard module

 

Function INDEXN(InputRange As Range, N As Integer) As Variant
' returns every N-th item from InputRange' --- Comment
' select the desired target range for the function and' --- Comment
' enter as an array function with Ctrl+Shift+Enter.' --- Comment
Dim ItemList() As Variant, c As Range, i As Long, iCount As Long
    i = 0
    iCount = 0
    ReDim ItemList(1 To InputRange.Cells.Count \ N)
    For Each c In InputRange
        i = i + 1
        If i Mod N = 0 Then
            iCount = iCount + 1
            On Error Resume Next
            ItemList(iCount) = c.Value
            On Error GoTo 0
        End If
    Next c
    INDEXN = ItemList
    If InputRange.Rows.Count >= InputRange.Columns.Count Then
        INDEXN = Application.WorksheetFunction.Transpose(INDEXN)
    End If
    Erase ItemList
End Function

 

img3

 

  • To get every nth item in range B1:B10, the formula is
  • {=INDEXN($A$1:$A$100, 10)}

Note: this is an array formula; hence, Ctrl + Shift + End keys must be pressed together

 

img4

 

  • To get every 7th value from range A1:A100; we will change the last argument from 10 to 7 & formula will be
  • {=INDEXN($A$1:$A$100, 7)}

 

img5

 

In this way, we can return every n-th item from one sheet to master sheet.

  • The formula in other sheet will contain sheet reference
  • {=INDEXN(Sheet1!$A$1:$A$100, 10)}

 

img6

 

Conclusion: With the help of above User-Defined Function, we can have flexibility to find out every nth item from one sheet to another.

 

image 48

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 
 



Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>