opposite of Merging data in multiple columns into one

[nitn28]

hi everyone here is code which Merge data in multiple columns into one

http://www.mcgimpsey.com/excel/index.html#xlvbamacros

i just want to do opposite of this

i have data in a column and i want to put it in different columns
(sequentially)

data in a column is separated by an empty cell
say i have data from a1 to a5 then empty cell again a7:a13
again empty cell like wise [n] number of data in "a"

so i want to put a1:a5 data in c1:c5
a7:a13 data in d1:d7 etc etc

i hope i wil get spport from this experts community

many thanx in advance
waiting 4 ur reply

 

[Jim Jackson]

Give this a whirl.

Sub MoveData()
Dim rng As Range
x = 3
With Cells

Set rng = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
Do
rng.Select
Selection.Copy
Cells(1, x).PasteSpecial
x = x + 1

rng.Offset(2, 0).Select
Selection.Find("").Offset(1, 0).Activate
If ActiveCell = "" Then
Exit Sub
End If
Set rng = .Range(ActiveCell.Address, ActiveCell.End(xlDown))
Loop
End With
End Sub--
Best wishes,

Jim

 

[Bernie Deitrick]

Nitn28,

If the values in column A are just values (not formulas):

Sub Nitn28()
Dim i As Integer

With Range("A:A").SpecialCells(xlCellTypeConstants, 23)
For i = 1 To .Areas.Count
Cells(1, i + 2).Resize(.Areas(i).Cells.Count).Value = _
.Areas(i).Cells.Value
Next i
End With
End Sub

HTH,
Bernie
MS Excel MVP

 

[nitn28]

THANKS ALOT

Mr. Bernie Deitrick & Mr. Jim Jackson

for your time , effort

it worked fine , Many Thanks again

wishing u great time ahead

with regrds
nitn28