Hi,
Try this which assumes Row 1 is ALWAYs empty as per your description.

Sub TranposeData()

Dim lastrow as long
Dim R as Long, C as integer

lastrow = Cells(Rows.Count, "A").End(xlUp).Row


For R = 2 To lastrow - 1
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


HTH

"nospaminlich" wrote:

Good evening

I'm trying to write a macro to transpose data to fill blank cells in a table
so in the example below it needs to find the first blank cell in row 2 (C2)
and copy the info in B3:B6 and transpose it into cells C2:F2. It then needs
to find the first blank cell in row 3 and repeat the sequence

A1 B C D E F
2 100
3 90 100
4 80 80 100
5 70 60 70 100
6 60 40 35 60 100

I need this to work on a table of any size so the macro needs to keep going
until it recognises it's at the end of the table.

I'm afraid I'm out of my depth here so any help would be much appreciated.

Thanks a lot

Kewa