how to merge the data of various cell into one cell (very huge quantity)

[mahaveer]

actually i have very huge data row wise and column wise so i need a macro for the following:Sheet1:

[pre]

A  B  C  D E F G H I J K L M N LIKE THIS SO ON...
1 J  K  H  K K K J K I U
2 H  K  U  P R G H J K F G B N Y

[/pre]
LIKE THIS SO ON...

like this so on...row wise too..this is now upto 25000 rows...data filled...


can you provide me a macro so that i can merge the data in one cell row wise for example i want to merge the above data in sheet2 in column A i.e. column A = JKHKKKJKIU LIKE THIS...but there is one problem too that there may be some rows length are short but some are long..as you can see the row 1 and the Row 2.

 

[vasim]

http://excelexperts.com/vba-tips-concatenate-range

 

[srinidhi]

Function CombineRow(myRange As Range)


Dim cell As Range

For Each cell In myRange

If Len(cell) > 0 Then

CombineRow = CombineRow & cell.Value

End If

Next cell


End Function


Type this formula where you want the result to appear & drag down

=CombineRow(A:A)enter

 

[shrivallabha]

Test this macro as well.

[pre]

Public Sub CombineRow()

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

'Currently it checks for first 256 columns, if you expect more than 256 then change it to
'suitable value
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sheet2").Range("A" & i).Value = _
Join(Filter(Evaluate("=IF(" & Range(Cells(i, 1), Cells(i, 256)).Address & "<>""""," & _
Range(Cells(i, 1), Cells(i, 256)).Address & ",""~"")"), "~", False), vbNullString)
Next i

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

End Sub

[/pre]