lets_excel
New Member
- Joined
- Jan 9, 2022
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
Hello all, looking for help on transposing a row but should keep the headers in the adjacent cell.
Here's a sample table
Result should look like this
This code i found somewhere is close but the headers are grouped
Looking forward to any help. Thanks!
Here's a sample table
Name | Number | Date |
---|---|---|
A | 1 | 1/1/2022 |
B | 2 | 1/2/2022 |
C | 3 | 1/3/2022 |
D | 4 | 1/4/2022 |
Result should look like this
Name | A |
Number | 1 |
Date | 1/1/2022 |
Name | B |
Number | 2 |
Date | 1/2/2022 |
Name | C |
Number | 3 |
Date | 1/3/2022 |
This code i found somewhere is close but the headers are grouped
VBA Code:
Sub Test()
Dim a, b(), i As Long, j As Long, x As Long
a = Range("A1", Cells(4, 1).End(xlToRight))
ReDim b(1 To (UBound(a, 1) - 1) * UBound(a, 2), 1 To 2)
For i = 1 To UBound(a, 2)
For j = 2 To UBound(a, 1)
x = x + 1
b(x, 1) = a(1, i)
b(x, 2) = a(j, i)
Next
Next
Range("E1").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub
Looking forward to any help. Thanks!