Hi Please help.
I need to combine data in 5 columns into 1 column as below. The columns to be merged are always identical in length up 20 000 lines.(Full example attached)
Raw data in columns A,B,C,D,E as below
A1 B1 C1 D1 E1
A2 B2 C2 D2 E2
Converted should be
A1
B1
C1
D1
E1
A2
B2
C2
D2
E2
Cheers
Frantic
Re: Merge 5 Columns Into 1
So you don't really want to merge you want to copy all 5 columns to one?
Re: Merge 5 Columns Into 1
Hi yes, sorry copy 4 columns into the first one retaining order.
Thanks for the quick responce
Frantic
Re: Merge 5 Columns Into 1
This should do what you want.
Dim dataRange As Range
Dim i As Long, j As Long, pointer As Long
Dim dataArray As Variant, outputArray As Variant, oneVal As Variant
With ThisWorkbook.Sheets("Raw Data")
Set dataRange = Range(.Cells(1, 5), .Cells(.Rows.Count, 1).End(xlUp))
End With
dataArray = dataRange.Value
ReDim outputArray(1 To dataRange.Rows.Count * dataRange.Columns.Count, 1 To 1)
For i = 1 To dataRange.Rows.Count
For j = 1 To dataRange.Columns.Count
pointer = pointer + 1
outputArray(pointer, 1) = dataArray(i, j)
Next j
Next i
ThisWorkbook.Sheets("Required").Range("b1").Resize(UBound(outputArray, 1), 1).Value = outputArrayRe: Merge 5 Columns Into 1
Hi Mike, Thanks for the effort. Unfortunately I am very much a VB beginner so please excuse. I copied accross an ran macro on 5 columns and received the following error. Run-time error '9' Subscript out of range. If I enter Debug then the line "With ThisWorkbook.Sheets("Raw Data")" is highlighted in the module.
Thanks Again
Frantic
Re: Merge 5 Columns Into 1
The sheet names Raw Data and Required were taken from the file you posted, they should be changed to match the sheet names of your worksheet.
Be sure to make a back up copy before trying any new VB routine on live data.
Re: Merge 5 Columns Into 1
Hi Mike. It works like a dream. This will really save me hours as it will allow me to preset data before capturing in one string as apposed to cel by cel. Thanks again, and all the best.
Frantic
You're welcome.
Don’t have an account yet? Register yourself now and be a part of our community!