PDA

View Full Version : Merge all data in 1 cell with format



parscon
05-17-2012, 09:47 AM
I have these data on A columns and i want merge them just to 1 cell but with this form :

check the example


A1
Wheel Loaders: L120(BM), L120B(BM), L120C(BM)

A2
Articulated Haulers: A20(BM), A20 6x4(BM)

A3
Excavators: EC200(Åkerman), EC200, EC230B(Åkerman)

A4
Motor Graders: G930, G940, G946, G960, G970, G976, G990


When Run THE VBA all the cell on Row A will be merge just to 1 cell with this format

Wheel Loaders:
L120(BM), L120B(BM), L120C(BM)
Articulated Haulers:
A20(BM), A20 6x4(BM)
Excavators:
EC200(Åkerman), EC200, EC230B(Åkerman)
Motor Graders:
G930, G940, G946, G960, G970, G976, G990

parscon
05-17-2012, 11:26 AM
it is done .


Sub Test()
Dim lLastRow As Long
Dim lRow As Long
Dim lLastCol As Long
Dim lCol As Long
Dim Cell As Range
Dim sTemp As String
lLastRow = Sheet1.UsedRange.Rows.Count
lLastCol = Sheet1.UsedRange.Columns.Count
For lRow = 2 To lLastRow
sTemp = Cells(lRow, "B")
For lCol = 3 To lLastCol
If Cells(lRow, lCol) = vbNullString Then
Exit For
Else
sTemp = sTemp & Chr(10) & Cells(lRow, lCol)
End If
Next lCol
Cells(lRow, "B") = sTemp
Next lRow
Range(Cells(1, "C"), Cells(1, lLastCol)).EntireColumn.Delete
End Sub