use same VBA code in a different column

nagasree

New Member
Joined
Oct 30, 2021
Messages
30
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.
 
Upvote 0
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.
 
Upvote 0
1674369537442.png

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

1674369687424.png
 
Upvote 0
Thank you. That is helpful.
Try this code:
VBA 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
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top