Changing data orientation

  • Thread starter Thread starter Marg
  • Start date Start date
M

Marg

How can I change data that is in consequential vertical
cells to cells that are horizontal?

In 7 vertical cells:

Sleep/Wake Disorders Center
Princeton Baptist Medical Center
817 Princeton Avenue SW
POB II, Suite 61
Birmingham, AL 35211-1399
Phone: 205-783-7378
Fax: 205-783-7386

Change to 7 cells that are horizontal across the page:
Sleep/Wake Disorders Center Princeton Baptist Medical
Center 817 Princeton Avenue SW POB II, Suite 61
Birmingham, AL 35211-1399 Phone: 205-783-7378
Fax: 205-783-7386

Thank you!
 
Tim has provided a manual method which would be easiest if you have only a few
to do.

If many and format is consistent, try this macro. In your case, Number of
Columns would be 7.

Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim j As Long
''Dim nocols As Integer
goagain:
Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")
If nocols = "" Or Not IsNumeric(nocols) Then GoTo tryover
For i = 1 To rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(rng.Row, "A")).ClearContents
Exit Sub
tryover:
Style = vbYesNo
msg = "You Have Cancelled " & Chr(13) _
& "Or Not Entered Criteria" & Chr(13) _
& "Do You Wish To Try Again?"
response = MsgBox(msg, Style)
Set srng = Nothing
If response = vbYes Then GoTo goagain
If response = vbNo Then Exit Sub
On Error GoTo 0
End Sub

Gord Dibben Excel MVP - XL97 SR2 & XL2002
 
Back
Top