Hi,
I note you made changes to the statement"lastrow= .." and the "For R
= " loop. Without seeing an example of your data, I am not sure if these work
or whether my original would. My original logic worked on the premise that
there are blanl cells to act as "end of data" markers.

Can you send me an example spreadsheet to look at?
)


"nospaminlich" wrote:

Hi

Thanks a lot for your help with this. I think I'm nearly there now.

Because my table is in the middle of a sheet with other data a few rows
above, below and in columns to the right the code was copying data from
beyond my table. After a lot of trial and error (all part of my giant
learning curve) I've modified the code so it seems to do exactly what I want
except when it gets to the bottom right cell it continues the process and
doesn't recognise that it's reached the end of the table.

Do I need to put something in somewhere that says if the cell to the right
and the cell below are blank then stop? If so how would I include that in
this macro?

Sub TranposeData()

Dim lastrow As Long
Dim R As Long, C As Integer
Dim rng As Range

ActiveCell.Offset(1, 1).Activate

' Set rng = ActiveSheet.UsedRange
' Or select cell in top left corner of matrix BEFORE calling macro
Set rng = ActiveCell

lastrow = ActiveCell.End(xlDown).Row

For R = rng(1).Row To lastrow
C = Cells(R, Columns.Count).End(xlToLeft).Column
Range(Cells(R + 1, C), Cells(lastrow, C)).Copy
Cells(R, C + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True
Next R

Application.CutCopyMode = False

End Sub


Thanks again for the help.

Kewa