How to use VBA to transpose data from single column to rows
Category: [Excel] Demo Available
How to use VBA to transpose data from single column to rows
Requirement:
The use has been given a .rtf file with thousands of customer records that the boss wants transferred to excel.
The data is not in a table, it's a strange mix of text boxes and drawn lines.
The user can copy and paste to Excel but all the data is pasted into a single column and the user needs to separate the data into a single row for each customer. There is some uniformity, each customer has five cells each and the customers are separated by a blank cell. The sheet looks like this:
a1. Customer 1 account no
a2. customer 1 name
a3. customer 1 address
a4. customer 1 post code
a5. customer 1 notes
a6. blank cell
a7. Customer 2 account no
a8. customer 2 name
etc.
The user can manually select cells A1 to A5 and then copy/transpose to cell B1 then keep going for each customer but that will take a long time with the amount of data.
Is there some way that the user can do this with VBA? E.g. is it possible to remove the blank cells then incrementally copy/transpose blocks of five cells for each customer into separate rows until there is no more data in column A?
The user is using Excel 2010 at work.
Solution:
Code:
Sub TransposeData()
Dim x, y(), i As Long, ii As Integer, lCnt As Long
x = ActiveSheet.UsedRange.Columns(1)
For i = 1 To UBound(x, 1) Step 6
lCnt = lCnt + 1: ReDim Preserve y(1 To 5, 1 To lCnt)
For ii = 1 To 5
y(ii, lCnt) = x(i - 1 + ii, 1)
Next
Next
[c1].Resize(, 5) = Array("Acc. No.", "Name", "Address", "Post Code", "Notes")
[c2].Resize(lCnt, 5) = Application.Transpose(y)
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by KjBox.
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 create excel VBA to transpose single row to multiple columns |
| 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 |
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.