VBA to transpose rows to column based on values in col A

[aravindhan_3]

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]

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]

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?