moving columns to rows....

[jbyrne]

IN SOME CASES I HAVE THE SAME NAME WITH MULTIPLE ADDRESSES. FOR EXAMPLE

COLUMN A B C D E
JOHN SMITH 123 MAIN ST ANYTOWN NJ 07622
JOHN SMITH 421 SOUTH DR ANYTOWN NJ 07624
JOHN SMITH 923 9TH AVE ANYTOWN NJ 07221
MARY DOE 123 5TH ST ANYTOWN OH 23473
MARY DOE 523 2ND ST ANYTOWN PA 34737

WHAT I WANT TO DO IS HAVE THE ADDRESSES GOING ACCROSS INSTEAD OF DOWNWARD IN THE COLUMN. EXAMPLE ABOVE (IF SAME NAME MOVE B2 TO F1)

ANY IDEAS?

 

[fb250r]

Hey,

I got your email on " Combine data" but no attachment. Can you please resend?

Thanks

Frank

 

[Jay Petrulis]

Resent, this time with file attached.

 

[jbyrne]

Hey Jay,

Just wanted to let you know that I used the macro today for the first time at work and it worked great! Thanks!

I noticed that on sheet2 there is a 1 space in the beginning of each columns B D & E as well as I K & L

I manually took out the space in each column by text to columns but I was wondering if there could be a correction in the macro you sent me?

Thanks
Jim

 

[Jay Petrulis]

On 2002-05-02 12:04, jbyrne wrote:
Hey Jay,

Just wanted to let you know that I used the macro today for the first time at work and it worked great! Thanks!

I noticed that on sheet2 there is a 1 space in the beginning of each columns B D & E as well as I K & L

I manually took out the space in each column by text to columns but I was wondering if there could be a correction in the macro you sent me?

Thanks
Jim

Hey, no fair! Your sample workbook only had two columns.

I am guessing that your original data has the problem. If so, change the code to look like this

With ActiveSheet
.UsedRange

'''add this to the routine here
For Each cell In ActiveSheet.UsedRange
If Not cell.HasFormula Then cell = Trim(cell)
Next cell
''''''''''''''

lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row

Also include the following at the top, with the rest of the variable declarations.

Dim cell as Range

You should be OK. Let me know if this corrects the problem.

Bye,
Jay

 

[jbyrne]

Hi Jay,

It worked perfect! I want to thank you for all your help!

Best regards,
Jim