how to collect some blocks into one block

Posted by EYAL on December 26, 2001 7:50 AM

i need to take all the first line blocks (from a1 to z1) and to put them all together in one block B2 (for example) with commas seperating between them .

Posted by Colo on December 26, 2001 8:25 AM

Hi Try This!

Sub test()
Dim i As Integer, MyStr As String, rng As Range, rng2 As Range
Set rng = Application.InputBox("Please select data cell", Type:=8)
Set rng2 = Application.InputBox("Please select paste cell ", Type:=8)
For i = 1 To Cells(rng.EntireRow.Row, 1).End(xlToRight).Column
MyStr = MyStr & Cells(1, i).Value & ","
Next
MyStr = Left(MyStr, Len(MyStr) - 1)
rng2.Value = MyStr
MsgBox "...Done"
End Sub