I'm trying to format labels to spredsheets. Somethimes i need to format
10,000 names and address and i need to do it fast. I would like some help
with this, I'm somewhat new at macros. So if you can help just email me or
post something.

Thanks,
Robert Shope

If you highlihgt the column that you want to format, you can then apply a
format enmasse.
HTH,
--
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"R.Shope" wrote:

I'm trying to format labels to spredsheets. Somethimes i need to format
10,000 names and address and i need to do it fast. I would like some help
with this, I'm somewhat new at macros. So if you can help just email me or
post something.

Thanks,
Robert Shope

From this:
John Smith
650 West Avenue
phoenix , AZ 33839

To look like this: John Smith 650 West Avenue phoenix , AZ 33839


"Gary L Brown" wrote:

If you highlihgt the column that you want to format, you can then apply a
format enmasse.
HTH,
--
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"R.Shope" wrote:

I'm trying to format labels to spredsheets. Somethimes i need to format
10,000 names and address and i need to do it fast. I would like some help
with this, I'm somewhat new at macros. So if you can help just email me or
post something.

Thanks,
Robert Shope

Hi Robert

It is still not entirely clear what you are wanting but it looks like
you have all your data in one column with 3 rows per contact and you
want to move each contact onto its own row. I am going to assume there
are no blank rows inbetween and that you data is consistent in the
number of rows per contact. Also assumed that data in column A with a
heading in row 1. SAVE before trying this:

Sub mvdata()
Dim eRow As Long
Dim i As Long

eRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To eRow - 2
Cells(i, 2).Value = Cells(i + 1, 1).Value
Cells(i, 3).Value = Cells(i + 2, 1).Value
Rows(i + 1).EntireRow.Delete
Rows(i + 1).EntireRow.Delete
Next i
End Sub

Hope this helps
Rowan

R.Shope wrote:
From this:
John Smith
650 West Avenue
phoenix , AZ 33839

To look like this: John Smith 650 West Avenue phoenix , AZ 33839

Hi,

If your data is consistent, i.e. is always grouped over 3, 4, or
whatever rows, you don't necessarily need a macro.

With your data listed starting in A10, enter the following.

B10 =INDIRECT("A"&(ROW()-ROW(A9))*3+10)
C10 =INDIRECT("A"&(ROW()-ROW(A9))*3+11)
D10 =INDIRECT("A"&(ROW()-ROW(A9))*3+12)


Then copy down for as many rows as you have groups. i.e if your data
consists of 100 groups over 300 rows, copy down for 100 rows.

If the data consists of 4 rows per record, just add a new formula in
E10 along the lines of the ones above.

HTH



On Mon, 7 Nov 2005 14:26:20 -0800, "R.Shope" wrote:


From this:
John Smith
650 West Avenue
phoenix , AZ 33839

To look like this: John Smith 650 West Avenue phoenix , AZ 33839


"Gary L Brown" wrote:

If you highlihgt the column that you want to format, you can then apply a
format enmasse.
HTH,
--
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"R.Shope" wrote:

I'm trying to format labels to spredsheets. Somethimes i need to format
10,000 names and address and i need to do it fast. I would like some help
with this, I'm somewhat new at macros. So if you can help just email me or
post something.

Thanks,
Robert Shope

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Rowan,

What i get sometimes is a file that has like 1000 names with addresses and i
need to inport to a program that uses excel. But the addresses i get are in
label format and i need it in spreedsheet format. There are blank lines
between address, but not always the same number of blanks.

"Rowan Drummond" wrote:

Hi Robert

It is still not entirely clear what you are wanting but it looks like
you have all your data in one column with 3 rows per contact and you
want to move each contact onto its own row. I am going to assume there
are no blank rows inbetween and that you data is consistent in the
number of rows per contact. Also assumed that data in column A with a
heading in row 1. SAVE before trying this:

Sub mvdata()
Dim eRow As Long
Dim i As Long

eRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To eRow - 2
Cells(i, 2).Value = Cells(i + 1, 1).Value
Cells(i, 3).Value = Cells(i + 2, 1).Value
Rows(i + 1).EntireRow.Delete
Rows(i + 1).EntireRow.Delete
Next i
End Sub

Hope this helps
Rowan

R.Shope wrote:
From this:
John Smith
650 West Avenue
phoenix , AZ 33839

To look like this: John Smith 650 West Avenue phoenix , AZ 33839