Ghosty wrote:
Hi there I've searched through the forum and found lots of solutions
for transposing vertical to horizontal data. However all the solutions
seem to be based on the fact that the number of rows of data is a set
amount.

For example I have the following data in one sheet:

Name
Address
Tel
Email
[emptyrow]
Name
Address
Tel
[emptyrow]
Name
Address
Tel
Email
[emptyrow]
Name
Address
[emptyrow]


And I wish for it to get transposed to the following:

Name Address Tel Email

Is there a way to automatically do this with a formula whilst using
that empty row as a designation to start a new row? And to just skip a
cell when a group of data does not have information, eg "no Tel
specified". With all the current formulas using OFFSET based on a
certain amount of rows this obviously does not work.

Thanks

Hi Ghosty,

This VBA code worked for me...

Public Sub TransposePersonalData()
Application.ScreenUpdating = False
Dim rngData As Range
Dim iLastRow As Long
Dim I As Long
Dim iDataColumn As Integer
iDataColumn = Selection.Column
iLastRow = Cells(Application.Rows.Count, iDataColumn).End(xlUp).Row
I = Selection.Row - 1
Do While ActiveCell.Row < iLastRow
I = I + 1
Set rngData = Range(ActiveCell, ActiveCell.End(xlDown))
rngData.Copy
Cells(I, iDataColumn + 1).PasteSpecial Transpose:=True
rngData.Cells(rngData.Cells.Count + 2, 1).Activate
Loop
End Sub

Try it out on a backup copy of your data first.

Before you actually run the macro you MUST make sure that the very
first name at the top of the list is selected. Proper function of the
code relies on this.

Also, the transposed data will appear in the four columns immediately
to the right of the original column of untransposed data.

To get the code into place...

1. Copy it

2. Press Alt + F11 or go Tools|Macro|"Visual Basic Editor" to get into
the Visual Basic Editor.

3. In the Visual Basic Editor go Insert|Module then paste the code into
the Code Module that appears

4. Press Alt + F11 or go File|"Close and Return to Microsoft Excel" to
get back to the usual Excel interface.

5.Save

6.If the workbook's Security level is set at High or Very High the
macro won't work and you will need to set the Security level to Medium.
To do that Go Tools|Macro|Security... and select Medium, then close and
reopen the workbook. Every time the workbook is opened you will need to
click on "Enable Macros" on the "Security Warning" dialog.

Ken Johnson