Multiiple column to single column

[Vijayonline2008]

I have created a new macro for shifting multiple columns of data to single column, given below. The problem is if there are so many columns it will take so much time to execute. I heard if we use an array it will be much faster. any one have any idea how to that


Sub Multiple_to_single()

Application.ScreenUpdating = False
Dim lastcoloumn As Long

lastcoloumn = ActiveSheet.UsedRange.Columns.Count
lastcoloumn = lastcoloumn - 1

For counter = 1 To lastcoloumn

ActiveCell.Offset(0, counter).Activate
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Cut
Rows("1:1").Select
Selection.Insert Shift:=xlDown

Next counter
Application.ScreenUpdating = False

End Sub

 

[Somendra Misra]

Hi,

Can you upload a sample file with say 10-20 columns of data and required output.

Regards,

 

[Debraj]

Hi Vijay,

Array works great with EXCEL-VBA, but it really hard to digest.. so Dont use array, if you don't understand.

check this one..

Sub ConvertRangeToColumnNonArray()
DEB = Range("a1").CurrentRegion
For I = 1 To UBound(DEB, 2) + 1
  ROY = ROY & Chr(1) & Join(Application.Transpose(Range(Cells(1, I), Cells(UBound(DEB), I))), Chr(1))
Next I
Range("a1").CurrentRegion.Clear
RAJ = Split(Mid(ROY, 2), Chr(1))
Range("A1:A" & UBound(RAJ)) = Application.Transpose(RAJ)
End Sub

 

[Marc L]

Vijay,

a good code never use Activate, Select, …

By avoiding that, your code will be faster !
​

 

[Vijayonline2008]

Thanks Debraj for your help. i just started learning vba and i'am just understanding the code.