Seperate row to Column.
 

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.

Seperate row to Column.
 

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


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"sigh" wrote in message ...
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.

Seperate row to Column.
 

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.

-----Original Message-----
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


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"sigh" wrote in message
...
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.


.

Seperate row to Column.
 

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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Sigh" wrote in message ...

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.

-----Original Message-----
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


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"sigh" wrote in message
...
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.


.

Seperate row to Column.
 

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.

-----Original Message-----
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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Sigh" wrote in message
...

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.

-----Original Message-----
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


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"sigh" wrote in message
...
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.


.



.

Seperate row to Column.
 

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



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Sigh" wrote in message ...

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.

-----Original Message-----
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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Sigh" wrote in message
...

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.

-----Original Message-----
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


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"sigh" wrote in message
...
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.


.



.