How to create Excel VBA to transpose single row to multiple columns
Category: [Excel] Demo Available
How to create Excel VBA to transpose single row to multiple columns
Requirement:
The user requires help to create codes that will transpose single row data to multiple columns.
Solution:
Code:
Private Sub CommandButton1_Click()
Dim x, xx, cnt1 As Long, cnt2 As Long, i As Long, ii As Long
[C8].CurrentRegion.ClearContents
x = [C5].CurrentRegion: cnt1 = 1: cnt2 = 1
ReDim xx(1 To UBound(x, 2) / 3, 1 To 3)
For i = 1 To UBound(x, 2) / 3
For ii = 1 To 3
xx(cnt1, ii) = x(1, cnt2)
cnt2 = cnt2 + 1
Next ii
cnt1 = cnt1 + 1
Next i
[C8].Resize(UBound(xx, 1), UBound(xx, 2)).Value = xx
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by apo.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to transpose single column into multiple columns and rows |
| How to consolidate data into single sheet from the selected spreadsheets |
| How to use a single 'date field' for various items in a data entry userform |
| How to use VBA to transpose data from single column to rows |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.