Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine 2 columns in a dynamic range into one
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine 2 columns in a dynamic range into one
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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine 2 columns in a dynamic range into one
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine 2 columns in a dynamic range into one
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine 2 columns in a dynamic range into one
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 tried to 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine 2 columns in a dynamic range into one
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine 2 columns in a dynamic range into one
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine 2 columns in a dynamic range into one
First one worked perfectly -
Thanks so much! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range for multiple columns | Excel Discussion (Misc queries) | |||
creating a dynamic range based on criteria, within a dynamic range, and passing it to a function | Excel Programming | |||
Drawing data from specific columns of a dynamic range | Setting up and Configuration of Excel | |||
Using specific columns of a dynamic range | Excel Programming | |||
Counting Columns in Dynamic Range | Excel Programming |