Hi again,

Am Wed, 5 Mar 2014 10:10:46 +0100 schrieb Claus Busch:

then try:
try:

Sub Concatenate()

Dim LRow As Long
Dim myArr(2) As Variant
Dim i As Long, j As Long

With Sheets("SquadLists Import")
For i = 2 To 6 Step 2
myArr(j) = .Cells(.Rows.Count, i).End(xlUp).Row
j = j + 1
Next
End With
LRow = WorksheetFunction.Max(myArr)

With Sheets("SquadLists")
.Range("A:C").ClearContents
.Range("A2:A" & LRow).Formula = _
"='Squadlists Import'!A2&"" ""&'Squadlists Import'!B2"
.Range("B2:B" & LRow).Formula = _
"='Squadlists Import'!C2&"" ""&'Squadlists Import'!D2"
.Range("C2:C" & LRow).Formula = _
"='Squadlists Import'!E2&"" ""&'Squadlists Import'!F2"
With .Range("A2:C" & LRow)
.Value = .Value
End With
End With
End Sub

or

Sub Concatenate2()

Dim LRow As Long
Dim LRowA As Long, LRowC As Long, LRowE As Long

With Sheets("SquadLists Import")
LRowA = .Cells(.Rows.Count, "A").End(xlUp).Row
LRowC = .Cells(.Rows.Count, "C").End(xlUp).Row
LRowE = .Cells(.Rows.Count, "E").End(xlUp).Row
End With

LRow = WorksheetFunction.Max(LRowA, LRowC, LRowE)

With Sheets("SquadLists")
.Range("A:C").ClearContents
.Range("A2:A" & LRowA).Formula = _
"='Squadlists Import'!A2&"" ""&'Squadlists Import'!B2"
.Range("B2:B" & LRowC).Formula = _
"='Squadlists Import'!C2&"" ""&'Squadlists Import'!D2"
.Range("C2:C" & LRowE).Formula = _
"='Squadlists Import'!E2&"" ""&'Squadlists Import'!F2"
With .Range("A2:C" & LRow)
.Value = .Value
End With
End With
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2