Formatting Worksheet

[Jdy]

I have a worksheet that I have downloaded a list of
businesses on. Unfortunately, the list is in one column,
the name of the business in row 1, the address is in row
2, the city is in row 3, etc. I would like to format the
information so that the name of the business is in one
column, the address is in the second column, the city is
in the third column, etc. I am pretty sure that there is
a way to do this, but do not know how. Also, I would like
to know how to change information that is a link. I would
like to change this information to regular type. Is there
a way to do this? Thanks for the help.

 

[Guest]

hi,
I am not sure what you are taking about with the link part.
but the other i can help.
copy the business name, address,city. then in cell a2( or
wherever to want it) click edit>pastespecial>transpose.

 

[jdy]

I am not sure that you understand exactly what I am
talking about. All of my information is in one column.
Row 1 has the name of the business, row 2 has the address,
row 3 has the city, state and zip. Row 4 has the name of
the second business, row 5 has the address of the second
business and so on.

When I try to transpose this, I get a message that
says, "the information cannot be pasted because the copy
area and the paste area are not the same size and shape.
Try one of the following:
* click a single cell and then paste
* select a rectangle that's the same size and shape and
then paste."

Let me know if you know of a way of getting around this.
Thanks.

 

[Don Lloyd]

Hi,

With your data starting at A1, the following will copy it to Columns B,D & E

Sub Test()
NewRw = 1
For X = 1 To 99 Step 3
Cells(NewRw, 2) = Cells(X, 1)
Cells(NewRw, 3) = Cells(X + 1, 1)
Cells(NewRw, 4) = Cells(X + 2, 1)
NewRw = NewRw + 1
Next
End Sub

If required change the 1 to 99 to suit your data rows.

Regards,
Don