Divide sheet into groups

[Jeff]

I am trying to sort names on a sheet depending on a number in D5. I have a
sheet from 1 to 112 that people can sign up on any line number for Student 1
to Student 112.

Student # Name
Student 1 Joe
Student 2
Student 3 Ron
Student 4 Bill
Student 5 Amie
Student 6
Student 7 Sue
Student 8 Mark
Student 9
Student 10 Billy
Student 11 Pat
Student 12
Student 13 Don
Student 14 Dave
Student 15 Jeff
Student 16 Harold
Student 17 Lou
Student 18
Student 19 Mike
Student 112

If D5 is 3 then I would like to sort them in groups of three. If the number
of groups is not an equally dividable number I need to add spaces to each of
the groups as evenly as possible.

Student # Name
Student 1 Joe
Student 3 Ron
Blank line
___________________
Student 4 Bill
Student 5 Amie
Student 7 Sue
____________________
Student 8 Mark
Student 10 Billy
Student 11 Pat
___________________
Student 13 Don
Student 14 Dave
Student 15 Jeff
___________________
Student 16 Harold
Student 17 Lou
Student 19 Mike
____________________

Or if D5 is 4 then the same thing sort them in groups of 4 dividing up the
spaces equally.

Student # Name
Student 1 Joe
Student 3 Ron
Student 4 Bill
Blank Line
___________________
Student 5 Amie
Student 7 Sue
Student 8 Mark
Blank Line
____________________
Student 10 Billy
Student 11 Pat
Student 13 Don
Student 14 Dave
___________________
Student 15 Jeff
Student 16 Harold
Student 17 Lou
Student 19 Mike
___________________

Any help would be appreciated.
Thanks Jeff.

 

[Joel]

Sub SplitSheet()

Set MyRange = Application.InputBox(prompt:="Select Range of students",
Type:=8)

StartCol = MyRange.Column
ColCount = MyRange.Columns.Count
StartRow = MyRange.Row
NumRows = MyRange.Rows.Count

Groupsize = Range("D5")

LastRow = StartRow + (Groupsize * Int(NumRows / Groupsize))

For RowCount = LastRow To (StartRow + 1) Step (-1 * Groupsize)
Range(Cells(RowCount, StartCol), Cells(RowCount, StartCol + ColCount -
1)).Insert _
Shift:=xlShiftDown
Next RowCount
End Sub

 

[Joel]

Sub RemoveSpace()

Set MyRange = Application.InputBox(prompt:="Select Range of students",
Type:=8)

StartCol = MyRange.Column
ColCount = MyRange.Columns.Count
StartRow = MyRange.Row
NumRows = MyRange.Rows.Count

LastRow = StartRow + NumRows - 1

For RowCount = LastRow To StartRow Step -1
If Cells(RowCount, StartCol) = "" Then
Range(Cells(RowCount, StartCol), Cells(RowCount, StartCol + ColCount -
1)).Delete _
Shift:=xlShiftUp
End If
Next RowCount


End Sub