How to copy and paste transpose using macro

Question

I am seeking for help I have thousands of unit ID that I need to copy and paste transpose manually. Here is the situation if column C Seq no. is the same for example Seq 1 then I need to copy column B unit ID and paste transpose to column D and I need to do that to the rest of the column

Did you try recording a macro whilst doing it? You could apply a filter on col C , then find the last row and then construct a range from Range("B2:B" & LastRow) and pass that to Application.WorksheetFunction.Transpose. There are limits to transpose though. See this question: https://stackoverflow.com/questions/20055784/best-workaround-for-vba-transpose-array-length-limit. This will help with transpose part [excel-vba-range-copy-transpose-paste](https://stackoverflow.com/questions/8852717/excel-vba-range-copy-transpose-paste) — QHarr, Jan 21 '18 at 14:00
Possible duplicate of [Excel VBA code for copy and transpose](https://stackoverflow.com/questions/41921479/excel-vba-code-for-copy-and-transpose) — ashleedawg, Jan 21 '18 at 16:48

score 2 · Accepted Answer · answered Jan 21 '18 at 15:59

2

Try this:

Sub TransposeUnitID()
    Dim data As Variant, seq_number As Integer, rw as Long

    data = Range("B2:C21").Value '~~>Update as necessary
    seq_number = data(1, 2)
    rw = 2

    For i = 1 To UBound(data)

        If data(i, 2) <> seq_number Then
            seq_number = data(i, 2)
            rw = i + 1
        End If

        Range("B" & rw).End(xlToRight).Offset(0, 1) = data(i, 1)
    Next i
End Sub

Notes:

Reads your data in as an array
Assume Seq is ordered (as per example)
Loop over array and keep track of Seq to print UnitID to correct cell

answered Jan 21 '18 at 15:59

Alex P

12,249
5
51
70

@ alex P thank you so much this really helps i just want to ask how can i extend the range data = Range("B2:C99").Value tried to maximize to thousand but got error i want to extend on the whole column c – ramon Jan 22 '18 at 06:57
Just replace the range with Range(“B2:C99”).value. – Alex P Jan 22 '18 at 08:05
Im getting error if i change to more than thousand Range(“B2:C1900”).value – ramon Jan 22 '18 at 10:08
It says run time error 1004: apllication- defined or object defined error – ramon Jan 22 '18 at 11:35
i got error on this line Range("B" & rw).End(xlToRight).Offset(0, 1) = data(i, 1) – ramon Jan 22 '18 at 12:17
Can you debug the line to see what the issue is e.g. what is `rw` set to? The issue is likely to do with `rw` or `i` not being set to a valid value. Or if you have gaps in your data (e.g blank row) then `End(xlToRight).Offset(0, 1)` will fail... – Alex P Jan 22 '18 at 12:22

How to copy and paste transpose using macro

1 Answers1