Divide list/Table in two

[Derek McGill]

I have a list of names and other data and i would like to divide into two :-
Put in a simple form,
1 1 - 4
2 2 - 5
3 3 - 6
4 7
5
6
7

 

[AlanSidman]

Option Explicit

Sub splitX()
    Dim lr As Long
    Dim half As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    half = lr / 2
    Range("A1:F" & half).Copy Range("H1")
    Range("A" & half + 1 & ":F" & lr).Copy Range("O1")

End Sub

 

[Derek McGill]

Thanks Alan

Option Explicit

Sub splitX()
    Dim lr As Long
    Dim half As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    half = lr / 2
    Range("A1:F" & half).Copy Range("H1")
    Range("A" & half + 1 & ":F" & lr).Copy Range("O1")

End Sub

This works for an Even number of players,

This slight change works for an Odd number of players,

Range("A1:F" & half - 1).Copy Range("H1")
Range("A" & half & ":F" & lr).Copy Range("O1")

Any way to combine both ??

 

[AlanSidman]

How about this:

Sub splitX()
    Dim lr As Long
    Dim half As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    half = lr / 2
    If half Mod 2 = 1 Then
    Range("A1:F" & half).Copy Range("H1")
    Range("A" & half + 1 & ":F" & lr).Copy Range("O1")
    Else:  Range("A1:F" & half - 1).Copy Range("H1")
    Range("A" & half & ":F" & lr).Copy Range("O1")
    End If
End Sub

 

[Derek McGill]

Can you talk me through "If half Mod 2 = 1"

 

[AlanSidman]

Determines if the variable Half is either even or odd.

Look her for a clear explanation of how it works http://www.excel-easy.com/vba/examples/mod-operator.html