Splitting one large column into many smaller ones

[linaeum66]

I have a large excel sheet with a list of email address in column A. This is the only column in the sheet. Column A has about 20,000 emails, and I would like to split this into multiple adjacent columns with exactly 400 emails per column. Is there an easy way to do this?

 

[Fluff]

How about

Sub linaeum66()
   Dim Ary As Variant
   Dim rr As Long, cc As Long, i As Long
   
   cc = 1
   Ary = Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(, 100)).Value2
   For rr = 400 To UBound(Ary) Step 400
      cc = cc + 1
      For i = 1 To 400
         If (rr + i) > UBound(Ary) Then Exit For
         Ary(i, cc) = Ary(rr + i, 1)
         Ary(rr + i, 1) = ""
      Next i
   Next rr
   Range("B1").Resize(rr - 1, cc).Value = Ary
End Sub

 

[My Aswer Is This]

Try this:

Sub Emails()
'Modified 2/10/2019 12:30:02 PM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim x As Long
x = 2
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 401 To Lastrow Step 400
    Cells(i, 1).Resize(400).Cut Cells(1, x)
    x = x + 1
    Next
Application.ScreenUpdating = True
End Sub

 

[CyrusTheVirus]

if your e-mails are starting in A1, then in B1 put the below formula and drag down 400 rows and over however many you need.

B1:

=IF(COLUMNS($B1:B1)=1,A1,OFFSET($A1,400*(COLUMNS($B1:B1)-1),))