Here's a UDF that returns an array. It gives the results you show.

If you want every 3rd number from a set of 10, and the results are to go in a
horizontal range, select 10 cells in the same row, say C1:L1, and type the
formula

=EveryNth(10,3)

and press CTRL+SHIFT+ENTER. If you want the results in a column, select 10
cells all in the same column, say A1:A10, and use the formula

=TRANSPOSE(EveryNth(10, 3))

The number of cells that you must select is equal to the value of the first
argument. For the 7th number out of 20, you would select 20 cells and the
formula would be =EveryNth(20,7) or =TRANSPOSE(EveryNth(20,7))

I have assumed your original data is a sequence of integers, starting with 1.
I use 0 as a flag to indicate that a number has already been selected.

Out of curiosity, what is the purpose of this?


Option Explicit

Function EveryNth(Num As Long, Nth As Long)
Dim i As Long
Dim j As Long
Dim k As Long
Dim x As Variant
Dim y As Variant

ReDim x(1 To Num)
For i = 1 To Num
x(i) = i
Next i

ReDim y(1 To Num)

i = 0
j = 0
k = 0

Do
i = i + 1
If i Num Then i = 1

If x(i) < 0 Then
k = k + 1
If k = Nth Then
j = j + 1
y(j) = x(i)
If j = Num Then Exit Do
x(i) = 0
k = 0
End If
End If
Loop
EveryNth = y

End Function


On Sat, 13 Nov 2004 15:11:02 -0800, "DeRizzio" <DeRizzio
@discussions.microsoft.com wrote:

Microsoft XP- Home Edition.

I want to create a macro that gives me the ability to list a set of numbers
and have them rearranged by a specific number.

Ex. #1
I have a set of 10 numbers, say:
1, 2, 3, 4, 5, 6, 7, 8, 9, 10
I want these numbers to be rearranged in the order of every third (3) number.
Therefore, the answer would be as follows:
3, 6, 9, 2, 7, 1, 8, 5, 10, 4


Ex. #2
I have a set of 20 numbers, say:
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20
I want these numbers to be rearranged in the order of every seventh (7)
number.
Therefore, the answer would be as follows:
7, 14, 1, 9, 17, 5, ............etc.

Best Regards!