|
Divide sheet into groups
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. |
Divide sheet into groups
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 "Jeff" wrote: 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. |
Divide sheet into groups
That works well. if I wanted to take the space out between the groups how
would I do this. Thank you Very Much! Jeff "Joel" wrote: 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 "Jeff" wrote: 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. |
Divide sheet into groups
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 "Jeff" wrote: That works well. if I wanted to take the space out between the groups how would I do this. Thank you Very Much! Jeff "Joel" wrote: 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 "Jeff" wrote: 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. |
| All times are GMT +1. The time now is 02:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com