Select multiple columns and paste data in one single cell by alt+F8

[Manish Kumawat]

Hi guys i want to run a code for the attached data pls help me.

 

[Hui]

Manish

You said Multiple columns, But I think you mean Multiple cells (A5:A18)

When you mean in 1 cell, do you mean like

ANAND
AJAY
ANANT
ASHVINI
But all in one cell or like,

ANAND, AJAY, ANANT, ASHVINI

 

[Hui]

Have you looked at using the Concat UDF?
http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

I would also have a look at the comments as there are several variations like Concatif() available in the comments

 

[Manish Kumawat]

yes Hui... i meant multiple cells but concat is not usefull as it gives continues and useless string of text.. Let me describe what i am thinking

suppose i have selected Cells (A1:A5)
i have simple vba

Sub manish()
selection.copy
Sheets("sheet2").select
Activesheet.paste
End sub

But it gives me the same data i need all the data of Cell (A1:A5) in just one cell like in A2 of sheet2.

but in proper formating just like entering data in excel spreadsheet

Thanks
Manish

 

[Hui]

Try this code:

Sub Copy_Range_Paste()

Dim c As Range

MyStr = ""
For Each c In Selection
  MyStr = MyStr + c.Text + Chr(10)
Next
Set a = Application.InputBox("Select destination cell", "Select destination cell", Type:=8)
MyStr = Left(MyStr, Len(MyStr) - 1)

Range(a.Address) = MyStr

End Sub

Select Range
Alt F8
Run Copy_Range_Paste

It prompts you for a destination range

 

[Manish Kumawat]

Thanks Hui.. :D you are such a genius