Combine 2 columns in a dynamic range into one

[PaulM]

Hi -

I have a dynamic list in a worksheet of Forename (A2) and Surname (B2). This list changes every quarter so is not a set length (i.e. the number of rows will change)

I need to run a macro that will combine the first name and surname of all entries in this list into another location (E1).

I have been using a simple worksheet function (=A2&" "&B2), but am encountering issues with other functions returning errors when there are formulas in cells I need to treat as blank cells - so I think a macro is the way forwards.

I have recorded a macro that works for the active cell but am struggling to apply this to the entire list:

ActiveCell.FormulaR1C1 = _
"='SquadLists Import'!RC[-8]&"" ""&'SquadLists Import'!RC[-7]"
Range("E3").Select

I would also need to get the macro to delete any previous list creation in E1 before it pastes the new list in (in the event that there are fewer names in a new quarter).

Appreciate any help on this, VBA is very much a language I am learning!!

 

[Claus Busch]

Hi Paul,

Am Tue, 25 Feb 2014 06:48:02 -0800 (PST) schrieb PaulM:

I have a dynamic list in a worksheet of Forename (A2) and Surname (B2). This list changes every quarter so is not a set length (i.e. the number of rows will change)

I need to run a macro that will combine the first name and surname of all entries in this list into another location (E1).

try:
Sub Concatenate()
Dim LRow As Long

With Sheets("SquadLists Import")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
.Columns("E").ClearContents
With .Range("E2:E" & LRow)
.Formula = "=A2&"" ""&B2"
.Value = .Value
End With
End With

End Sub


Regards
Claus B.

 

[ryasportscience]

Hi Claus -

that worked a treat - thank you!!

How would I amend the code to paste the result to the same location in a seperate worksheet names "Lists" ?

All the best

 

[Claus Busch]

Hi,

Am Tue, 4 Mar 2014 14:50:21 -0800 (PST) schrieb
(e-mail address removed):

How would I amend the code to paste the result to the same location in a seperate worksheet names "Lists" ?

try:

Sub Concatenate()
Dim LRow As Long

LRow = Sheets("SquadLists Import").Cells(Rows.Count, 1).End(xlUp).Row

With Sheets("Lists")
.Columns("A").ClearContents
With .Range("A2:A" & LRow)
.Formula = "='Squadlists Import'!A2&"" ""&'Squadlists
Import'!B2"
.Value = .Value
End With
End With

End Sub


Regards
Claus B.

 

[ryasportscience]

Great! very nearly there. I have applied the same code to run this for 3 seperate lists at teh same time (3 lists are all of different lengths)

So the code I am using is ....



Sub Concatenate()

Dim LRow As Long

LRow = Sheets("SquadLists Import").Cells(Rows.Count, 1).End(xlUp).Row

'Podium List

With Sheets("SquadLists")
.Range("A2:A" & LRow).ClearContents
With .Range("A2:A" & LRow)
.Formula = "='Squadlists Import'!A2&"" ""&'Squadlists Import'!B2"

.Value = .Value
End With
End With


'PP List

With Sheets("SquadLists")
.Range("B2:B" & LRow).ClearContents
With .Range("B2:B" & LRow)
.Formula = "='Squadlists Import'!C2&"" ""&'Squadlists Import'!D2"

.Value = .Value
End With
End With


'Historical List

With Sheets("SquadLists")
.Range("C2:C" & LRow).ClearContents
With .Range("C2:C" & LRow)
.Formula = "='Squadlists Import'!E2&"" ""&'Squadlists Import'!F2"

.Value = .Value
End With
End With

End Sub




However the range to copy gets set by this line meaning that if the second list is longer than the first not everything gets copied over. I have triedto include this within each WITH statement, but it dosent allow multiple Dim LRow statements in the same process.


Dim LRow As Long

LRow = Sheets("SquadLists Import").Cells(Rows.Count, 1).End(xlUp).Row



Thanks so much for your help!

 

[Claus Busch]

Hi,

Am Wed, 5 Mar 2014 00:32:45 -0800 (PST) schrieb
(e-mail address removed):

Great! very nearly there. I have applied the same code to run this for 3 seperate lists at teh same time (3 lists are all of different lengths)

then try:

Sub Concatenate()

Dim LRow As Long
Dim myArr(5) As Variant
Dim i As Long

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

With Sheets("SquadLists")
.Range("A2:C" & LRow).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


Regards
Claus B.

 

[Claus Busch]

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.

 

[ryasportscience]

First one worked perfectly -

Thanks so much!