Seperate row to Column.

[sigh]

Hi,
I got the address file. The all in one Colume (A)
with alot of rows. Each address file seperate by the empty
row. How can I got the each address file from each row to
columns. Examples of two address record;

Column A
========

AAA xxx
123 st
sss,fff
xxxx


BBB ZZZ
345 sr
ggg, hhhh
jjdld

TO

Column A Column B Column C Column D
===============================================
AAA xxx 123 st sss, fff xxxx
BBB ZZZ 345 sr ggg, hhh jjdld

How can i write a macro for this?

Any help would be very appreciate.

 

[Ron de Bruin]

Try this

With the data start in row 1
it will transpose the data to column C:F

Sub test()
Dim a As Long
Dim b As Long
b = 0
For a = 1 To Range("A" & Columns.Count).End(xlUp).Row Step 5
b = b + 1
Range("A" & a).Resize(4, 1).Copy
Range("C" & b).PasteSpecial xlPasteValues, , False, True
Next
End Sub

 

[Sigh]

Thanks for help. I miss some information here. some of the
address file has different rows. Some has 4 row and some
has 5 or 6 rows. How can I change your code to solved this
problems.

 

[Ron de Bruin]

Try something like this Sigh

Sub test()
Dim Copyrw As Long
Dim lrw As Long
Dim frw As Long
frw = 1
For frw = 1 To Range("A" & Columns.Count).End(xlUp).Row
Copyrw = Copyrw + 1
lrw = Range("A" & frw).End(xlDown).Row
Range("A" & frw).Resize(lrw - frw + 1, 1).Copy
Range("C" & Copyrw).PasteSpecial xlPasteValues, , False, True
frw = lrw + 1
Next
Application.CutCopyMode = False
End Sub

 

[Sigh]

Yes, it works. But it only convert up to row 256 and then
stop. The file in the column A has the text up to 400 rows.

 

[Ron de Bruin]

Ahhh

My mistake

For frw = 1 To Range("A" & Columns.Count).End(xlUp).Row

must be

For frw = 1 To Range("A" & Rows.Count).End(xlUp).Row