Return every n-th item

The custom function below will return every n-th item from a range. This is an array function, and it's used like this: Select the range you want the function to return its result to, e.g. B1:B10.
Enter the function like this: = INDEXN(A1:A1000, 10)
Press Ctrl+Shift+Enter (not just Enter) when you are finished.
The function will now return the first 10 (B1:B10=10 cells) of every 10-th item in the range A1:A1000.

Function INDEXN(InputRange As Range, N As Integer) As Variant
' returns every N-th item from InputRange
' select the desired target range for the function and
' enter as an array function with Ctrl+Shift+Enter.
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

Document last updated 2000-02-05 12:44:52 Printerfriendly version