Transpose 2nd line, 3 cells, to first open column - thend elete all 2nd blank rows

[Wizerd]

All,

I have a sheet that contains content in the first/odd rows from A1 to M1. The second/even rows have content from A2 to C2. I wish to transpose A2 > N1, B2 > O1 and C2 >P1.

Any help would be appreciated.

1	1BD1209715	180309	14:37	181156095	162012	P	84	6GC69		GAN	12	6
POST90SP	*DVT	GSIP
2	1BP1242049	180301	08:42	181154414	160466	P	84	1GZ69		GAN	13	14
POST90SP	*DVT	GSIP
3	1BP1242423	180302	12:00	181154842	160797	P	84	6GC69		GBA	12	13
POST90SP	*DVT	GSIP

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Dryver14]

does it always say POST90SP?

 

[Wizerd]

does it always say POST90SP?

No. The 3 cells can have any data in them.

 

[Dryver14]

then do you want the empty rows deleting. also what is the very first line, is it 1 or 2 dont want to run on the wrong line.

 

[Dryver14]

Dim i As Long
Dim lastrow As Long


    lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = lastrow To 2 Step -1
        If Cells(i, 4) = "" Then
            Cells(i, 1).Resize(1, 3).Copy Cells(i - 1, 14)
            Cells(i, 1).EntireRow.Delete
        End If
    Next i
        
End Sub

 

[Wizerd]

‘1’ is in A1. And yes’ after the transpose I need the blank line deleted. Thanks for the quick responses.

 

[Dryver14]

the above should do what you want, try it on a copy of workbook or worksheet

 

[Wizerd]

Works great. If I wanted to start this transpose at A6 instead of A1 (different worksheet...same principle)

 

[Dryver14]

yes, it runs backwards though so where I say For i = lastrow To 2 Step -1 you change the 2 to what ever row number you want to finish on working from bottom to top

 

[Wizerd]

Works like a charm. Thanks for the help!