PDA

View Full Version : [SOLVED] VBA to transpose rows to column based on values in col A



aravindhan_3
03-03-2016, 04:01 AM
Hi,

I have countries in column A and items sold in col B, I want to transport the same into columns, for example the country India should be in row 1, and below all those items should be there, China in row 2 & the items below that.

I have attached the sample file for reference,

please help me with the macro for this, I have got about 100000 rows & 65 countries.

Regards
Arvind

Bob Phillips
03-03-2016, 04:23 AM
Public Sub Test()
Const test_column = "A"
Dim lastrow As Long
Dim lastcol As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

lastrow = .Cells(.Rows.Count, test_column).End(xlUp).Row
For i = lastrow To 2 Step -1

If .Cells(i, test_column).Value = .Cells(i - 1, test_column).Value Then

.Cells(i, test_column).Offset(0, 1).Resize(, 100).Copy .Cells(i - 1, test_column).Offset(0, 2)
.Rows(i).Delete
End If
Next i

lastrow = .Cells(.Rows.Count, test_column).End(xlUp).Row
lastcol = .UsedRange.Columns.Count
.Range("A1").Resize(lastrow, lastcol).Copy
.Range("A1").Offset(lastrow).PasteSpecial Paste:=xlPasteAll, Transpose:=True

.Rows(1).Resize(lastrow).Delete
.Columns(test_column).Delete
End With

Application.ScreenUpdating = True
End Sub

aravindhan_3
03-03-2016, 06:52 AM
Hi,
It worked:) thanks a million.

however it deletes column A & B, what if I want to keep them just in case and paste the result in column D?