» Return every n-th item using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions 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