use same VBA code in a different column

[nagasree]

Hi, I have a code which runs on column A, but i need the code to ignore column A and start working from column B.

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

 

[Joe4]

I don't like trying to figure out what someone is trying to do by trying to trying to reverse engineer their code, especially when they have provided no data or no explanation.
How a code behaves is very dependent on the data structure, which you have not provided.
Can you please post a sample of your data, and show us what you are trying to do?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Micron]

Methinks change
.Cells(rw + 1, 1) = .Cells(rw, 1).Value2
.Cells(rw + 1, 2) = .Cells(rw, cl).Value2
to
.Cells(rw + 1, 2) = .Cells(rw, 1).Value2
.Cells(rw + 1, 3) = .Cells(rw, cl).Value2
or
.Cells(rw + 1, 2) = .Cells(rw, 2).Value2
.Cells(rw + 1, 3) = .Cells(rw, cl).Value2

Please post code within code tags (vba button on posting toolbar) to maintain indentation and readability.

 

[nagasree]

This is my raw data and i want to use the above code and get a output like below

 

[Joe4]

Thank you. That is helpful.
Try this code:

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