On Jun 28, 10:03 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
give this a try:

Option Explicit
Sub Split_Col()
Dim ws2 As Worksheet
Dim i As Long, z As Long, q As Long
Dim lastrow As Long
Dim colGroup As Long
Dim remainder As Long
Set ws2 = Worksheets("Sheet1")
lastrow = ws2.Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
colGroup = (lastrow - 1) \ 40 ' determine number of groups
remainder = (lastrow - 1) Mod 40 ' partial group
Debug.Print colGroup & " " & remainder
z = 1
On Error Resume Next
For i = colGroup To 1 Step -1
If i 1 Then
ws2.Range("A41:A41").Resize(40).Copy
ws2.Cells(1, z + 1).PasteSpecial xlPasteAll
ws2.Rows("41").Resize(40).EntireRow.Delete
ws2.Columns(z + 1).Resize(, 1).AutoFit
z = z + 1
Else
If remainder = 0 Then GoTo Xit
ws2.Range("A41:A41").Resize(remainder + 1).Copy
ws2.Cells(1, z + 1).PasteSpecial xlPasteAll
ws2.Rows("41").Resize(remainder + 1).EntireRow.Delete
ws2.Columns(z + 1).Resize(, 1).AutoFit
End If
Next
Xit:
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

--

Gary

wrote in message

ups.com...

Hi Gary,

Thank you for the quick reply and it works well.

Just wondering though, if i don't want the header, and my data starts
at A1 cell. How should the coding be? Also i need the number of data
in a column to be 40 instead of 48.

Appreciate your help again because i know nothing about programming.
Thanks!

Got it now. Works perfectly. Thanks!