Spread data evenly in excel list

[Aurillius]

Hi everyone,
I have a list of items that I would like spaced as evenly as possibly in a set number of rows.

Eg. 7 items need to be spread over 51 rows (much larger in real circumstance).

I hope that there is procedure that can work for several different circumstances.

1. Apple
2.
3. Banana
4.
5.
6. Orange
7.
8. Pear
9.
10.

Thanks,
Mark

 

[SkipVought]

HI,

Well what approch have you tried?

What is the actual structure of your data?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

...anyhow, this might get you started in the right direction, assuming your row counts are in column A and your items are in column B all starting in row 1...

Sub SpreadEm()
    Dim i As Long, j As Long, k As Long, l As Long, m As Long
    
    i = Application.CountA([A:A])   'total rows
    j = Application.CountA([B:B])   'total items to spread
    
    k = Int(i / j) + 1
    
    For l = 1 To j
        m = (l - 1) * k
        
        Select Case m
            Case Is < 1
                m = 1
            Case Is > i
                m = i
            Case Else
        End Select
        
        Cells(l, "C").Value = m
    Next
    
    For l = j To 2 Step -1
        Cells(Cells(l, "C").Value, "B").Value = Cells(l, "B").Value
        Cells(l, "B").ClearContents
    Next
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]