Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concat a three column range into one cell Then Next...
This works pretty good, I select the cells in three adjacent columns and run the code, all the data is in one cell, AU2 in this case. I would like to be able to run this code down column AU, say 25 rows. (adjustable, might be 60 next column I use it in) The three cells I want to concat code to join are AU.Offset(0, -6).Resize(1, 3). And there are blank cells. Where blank means all three of the cells to be joined will be blank Example data is something like this. GRP SN 2500 STIS * 300 * PN 1 Where the * denotes the next column (if it posts ok here). Current result for the one-cell-at-a-time process looks like this in AU2. GRP SN 2500 STIS300PN 1 I would prefer that the spaces were remove in the process to look like this. GRPSN2500STIS300PN1 Thanks, Howard Option Explicit Sub ConcatTest() Range("AU2").ClearContents Dim rngC As Range For Each rngC In Selection If Len(rngC) = 0 Then [AU2] = [AU2] & " " Else [AU2] = [AU2] & rngC.Text End If Next 'Range("AU2").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concat a three column range into one cell Then Next...
Hi Howard,
Am Wed, 16 Oct 2013 03:22:29 -0700 (PDT) schrieb Howard: I would like to be able to run this code down column AU, say 25 rows. (adjustable, might be 60 next column I use it in) The three cells I want to concat code to join are AU.Offset(0, -6).Resize(1, 3). And there are blank cells. Where blank means all three of the cells to be joined will be blank try: Sub Test() Dim LRow As Long Dim i As Long Dim myStr As String LRow = Cells(Rows.Count, "AO").End(xlUp).Row For i = 2 To LRow myStr = Replace(Cells(i, "AO") & Cells(i, "AP") & _ Cells(i, "AQ"), " ", "") Cells(i, "AU") = myStr Next 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
|
|||
|
|||
Concat a three column range into one cell Then Next...
On Wednesday, October 16, 2013 3:36:28 AM UTC-7, Claus Busch wrote:
Hi Howard, Am Wed, 16 Oct 2013 03:22:29 -0700 (PDT) schrieb Howard: I would like to be able to run this code down column AU, say 25 rows. (adjustable, might be 60 next column I use it in) The three cells I want to concat code to join are AU.Offset(0, -6).Resize(1, 3). And there are blank cells. Where blank means all three of the cells to be joined will be blank try: Sub Test() Dim LRow As Long Dim i As Long Dim myStr As String LRow = Cells(Rows.Count, "AO").End(xlUp).Row For i = 2 To LRow myStr = Replace(Cells(i, "AO") & Cells(i, "AP") & _ Cells(i, "AQ"), " ", "") Cells(i, "AU") = myStr Next End Sub Regards Claus B. Sheer magic! Works great. As always, thank a lot. Regards, Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if row has duplicates, concat entries in another column? | Excel Discussion (Misc queries) | |||
Concat rows - | Excel Programming | |||
Macro to concat row | Excel Programming | |||
Remove #VALUE! from ConCat Range Function | Excel Worksheet Functions | |||
Loop through column headers to search from column name and get cell range | Excel Programming |