Transpose in place in excel vba

Question

I have a raw data which needs to be split into different columns, the raw data looks like

and i want the output to look like below.

I have a code which does the work when the Column A is not present, I need to modify it along with the column A

the code is

Sub transpose_in_place()
    Dim rw As Long, cl As Long
    With ActiveSheet
        For rw = .Cells(rows.Count, 1).End(xlUp).Row To 2 Step -1
            For cl = .Cells(rw, Columns.Count).End(xlToLeft).Column To 3 Step -1
                If Not IsEmpty(.Cells(rw, cl)) Then
                    .rows(rw + 1).Insert
                    .Cells(rw + 1, 1) = .Cells(rw, 1).Value2
                    .Cells(rw + 1, 2) = .Cells(rw, cl).Value2
                    .Cells(rw, cl).clear
                End If
            Next cl
        Next rw
    End With
End Sub

Im not sure how to modify the code, can someone help me with it. Thanks in advance

user3598756 · Accepted Answer · 2023-01-22 07:15:57Z

1

Option Explicit

Sub transpose_in_place()
    Dim rw As Long, cl As Long
    With ActiveSheet
        For rw = .Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
            For cl = .Cells(rw, Columns.Count).End(xlToLeft).Column To 4 Step -1
                If Not IsEmpty(.Cells(rw, cl)) Then
                    .Rows(rw + 1).Insert
                    .Cells(rw + 1, 1) = .Cells(rw, 1).Value2
                    .Cells(rw + 1, 2) = .Cells(rw, 2).Value2
                    .Cells(rw + 1, 3) = .Cells(rw, cl).Value2
                    .Cells(rw, cl).Clear
                End If
            Next cl
        Next rw
    End With
End Sub

answered Jan 22, 2023 at 7:15

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3598756 Over a year ago

BTW, did you have a chance to try my solutions in those other two post of yours: stackoverflow.com/questions/75195166/… and stackoverflow.com/questions/75193871/…?

N S Over a year ago

Than you so much, It works exactly as needed.

Collectives™ on Stack Overflow

Transpose in place in excel vba

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related