Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10
Default 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!


  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10
Default Combine 2 columns in a dynamic range into one

First one worked perfectly -

Thanks so much!
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamic Range for multiple columns Robin Excel Discussion (Misc queries) 8 April 3rd 23 02:26 PM
creating a dynamic range based on criteria, within a dynamic range, and passing it to a function [email protected] Excel Programming 0 October 9th 07 05:22 PM
Drawing data from specific columns of a dynamic range Darren Setting up and Configuration of Excel 3 March 3rd 06 07:53 AM
Using specific columns of a dynamic range Darren Excel Programming 0 March 2nd 06 11:27 AM
Counting Columns in Dynamic Range ExcelMonkey[_102_] Excel Programming 1 March 4th 04 11:22 PM


All times are GMT +1. The time now is 05:39 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"