One column to multiple columns - with a twist

[Tenacious Lee]

Hello to all Excel experts in internet-land,

I have an interesting twist on a problem that has been solved in lots
of previous posts. I have a huge column of numerical data, with
'chunks' of cells of data separated by variably-sized 'chunks' of blank
cells. I would like to be able to apply a macro that will wade through
the data, splitting the big column into multiple columns, with each
'chunk' of data in its own individual column. Hopefully this will
clarify things for you:

Raw data:

1
2
3
4

4
5
7

12
3
5
67
7

Finished product:

1 4 12
2 5 3
3 7 5
4 67
7

Any help or code snippets from a curious expert would be greatly
appreciated!

 

[Dave Peterson]

Are all those values just plain old constants--not formulas???

Option Explicit
Sub testme()

Dim myRng As Range
Dim myArea As Range
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

With CurWks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No constants"
Exit Sub
End If

oCol = 0
For Each myArea In myRng.Areas
oCol = oCol + 1
myArea.Copy _
Destination:=NewWks.Cells(1, oCol)
Next myArea
End With

End Sub

 

[Guest]

Sub SeparateColumns()
dim rng as Range, ar as Range
dim col as Long
col = 2
set rng = columns(1).SpecialCells(xlConstants)
if rng.Areas.count > 255 then
msgbox "too many blocks"
exit sub
end if

for each ar in rng.Areas
ar.copy cells(1,col)
col = col + 1
Next ar
' columns(1).Delete
End sub

 

[Tenacious Lee]

Good gracious you guys rock.

Thanks for the prompt (and spot-on) response.

-Lee