Transpose Macro

chrisscotty

Board Regular
Joined
Jul 9, 2002
Messages
87
Hello All...

I have yet another macro I would like to get data in to a contact manager..if any of you know of a tool that helps you transpose I would also be curious to try it out as I often have data I need to transpose so I can export data via tab delimited file in to a contact manager.


1. Ms. Mandy Tezizina, RPA Transportation Specialist
Mulixe
430 - 55 Metcalfe Street, Ottawa, ON K1P 6L5
Phone: 1-913-230-2251 x 228 Fax: 1-913-563-0878 Email: mandy_teziz@mulixe.ca
Website: www.mulixe.ca 

basically the data looks like this...and I want it all to be like this



mandy tezizine |transportation specialist| etc

I want to attach an excel file of what it looks like in excel but I dont see that option..hmm
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
You can't attach files here. You can, however, use the HTML Maker add-in and provide a sort of screen shot. The link to download it is at the bottom of this page.
 

chrisscotty

Board Regular
Joined
Jul 9, 2002
Messages
87
for transpose.xls
ABCD
11. Ms. Mandy Tezizina, RPATransportation Specialist
2Mulixe
3430 - 55 Metcalfe Street, Ottawa, ON K1P 6L5
4Phone: 1-913-230-2251 x 228 Fax: 1-913-563-0878 Email: mandy_teziz@mulixe.ca
5Website: www.mulixe.ca
62. Mr. Pet Buzzinet, CRCEVocational Rehabilitation Worker
7Darlington Community Health Form
8900 Merrivale Road, Kawa, OHIO 24698
9Phone: 1-413-722-9731 x 316 Fax: 1-413-722-8244 Email: pbuzzzinet@darlington.us
10
11I want the data to look like below
12
13mandy texzzinaTransportation Specialist430-55 metcalfe
Sheet1
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
You need to strip out the
Code:
 &
tags for the HTML to work.

Also something to keep in mind: you may not want to post personal information on a public board. Just make some up.

This might work for you:

Code:
Sub Transpose()
    Application.Goto Reference:=[A1]
    Do While ActiveCell <> ""
        If ActiveCell.Offset(1, 0) = "" Then ActiveCell.Offset(1, 1).Cut ActiveCell.Offset(0, 2)
        If ActiveCell.Offset(2, 0) = "" Then ActiveCell.Offset(2, 1).Cut ActiveCell.Offset(0, 3)
        If ActiveCell.Offset(3, 0) = "" Then ActiveCell.Offset(3, 1).Cut ActiveCell.Offset(0, 4)
        If ActiveCell.Offset(4, 0) = "" Then ActiveCell.Offset(4, 1).Cut ActiveCell.Offset(0, 5)
        If ActiveCell.Offset(5, 0) = "" Then ActiveCell.Offset(5, 1).Cut ActiveCell.Offset(0, 6)
        Range(ActiveCell.Offset(1, 0), ActiveCell.End(xlDown).Offset(-1, 0)).EntireRow.Delete
        ActiveCell.End(xlDown).Select
    Loop
End Sub

Hope that helps,

Smitty
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,561
Messages
5,765,109
Members
425,260
Latest member
worldbfreebase

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top