# Splitting one large column into many smaller ones

#### linaeum66

##### New Member
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?

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Code:
``````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``````

Try this:
Code:
``````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``````

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:
Code:
``=IF(COLUMNS(\$B1:B1)=1,A1,OFFSET(\$A1,400*(COLUMNS(\$B1:B1)-1),))``

Last edited:

Replies
0
Views
196
Replies
8
Views
144
Replies
0
Views
299
Replies
7
Views
65
Replies
13
Views
140

1,203,242
Messages
6,054,350
Members
444,718
Latest member
r0nster

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back