Concatenateing and entire row automaticalls

T

threearabawys

New Member
Joined
Nov 23, 2014
Messages
34
Hello everybody


I need to automatically CONCATENATE row 8 of my worksheet into cell A21 of the same worksheet.
The values in row 8 are dynamic so the concatienation should work life a function or a module.

Does anybody have an idea on how this can be achieved ?


Thanks in advance
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Give this event code a try...

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim LastColumn As Long, JoinedText As String
  [COLOR=#b22222][B]Const Delimiter = ", "[/B][/COLOR]
  If Not Intersect(Target, Rows(8)) Is Nothing Then
    LastColumn = Cells(8, Columns.Count).End(xlToLeft).Column
    If LastColumn = 1 Then
      JoinedText = Range("A8").Value
    Else
      JoinedText = Join(Application.Index(Range("A8").Resize(, LastColumn).Value, 1, 0), Chr(1))
    End If
    Range("A21").Value = Replace(Replace(Application.Trim(Replace(Replace(JoinedText, _
                         " ", Chr(2)), Chr(1), " ")), " ", Delimiter), Chr(2), " ")
  End If
End Sub

Note: You did not say what delimiter you wanted between the values, so I assumed a comma followed by a space. If that is not correct, you can change the delimiter to whatever you want in the highlighted line of code above.


HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Last edited:
Upvote 0
Hello Rick :), you always save my behind

Thank you for the code, but after installing it nothing happened... I forgot to mention that the values in the range in question are generated using a function.... I don't need to use a delimiter, and I can work with a macro if it's not possible to do it automatically.

Thanks for the help :)
 
Upvote 0
threearabawys said:
I use...

=IFERROR(IF(A13="Blue",1,0),"")

to generate a value between 1 and 0 based on the value of A13
Click to expand...

How is the "Blue" value getting into cell A13... is it typed in by the user or do you have formula in that cell as well? If a formula, can you show us that one? And if there are cell reference in it, how do those cell references get their values?
 
Upvote 0
Rick Rothstein said:
How is the "Blue" value getting into cell A13... is it typed in by the user or do you have formula in that cell as well? If a formula, can you show us that one? And if there are cell reference in it, how do those cell references get their values?
Click to expand...
Actually, never mind on that... the following is minimally less efficient than what I was attempting to do, but it should work without any problems. It is still event code and is installed in the same way as the Change event code that I gave you earlier (which you should delete, by the way).
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim LastColumn As Long, JoinedText As String
  LastColumn = Cells(8, Columns.Count).End(xlToLeft).Column
  Application.EnableEvents = False
  If Len(Range("A21")) = 0 Then
    Range("A21").NumberFormat = "General"
  ElseIf LastColumn = 1 Then
    Range("A21").NumberFormat = "@"
    Range("A21").Value = Range("A8").Value
  Else
    Range("A21").NumberFormat = "@"
    Range("A21").Value = Join(Application.Index(Range("A8").Resize(, LastColumn).Value, 1, 0), "")
  End If
  Application.EnableEvents = True
End Sub
 
Upvote 0
Rick Rothstein said:
How is the "Blue" value getting into cell A13... is it typed in by the user or do you have formula in that cell as well? If a formula, can you show us that one? And if there are cell reference in it, how do those cell references get their values?
Click to expand...


It's getting the blue value as a result of another formula
=IF(B12>=73,"Red","Blue")

To make sense of this...
I have a row which cells get their interior color changed by a vb script, I then use a module to get the color index value of each cell on that row and put it in the row below it... I have a two color scheme going on so the cells are all either Red or Blue.
now on the A11 cell I tell it to count the cells which interior color is Blue in the first 15 cell range of the "color index" row, in B11 i do the same only for the cells which interior color is Red.
now i get the percentage of the A11 cell to the to the total number of cells being processed (A11*100/15), and the same for the B11 cell and put the resulting values in the cells below (row 12), then finally I tell A13 to check and see if B12 which is the percentage of red cells in a 15 cell range is equals to/or greater than 73 , if true it gives a value of red and if false it gives a value of blue....


the formulas are as follows:
A6 to O6 =Colorindex(cell address)
A11 =COUNTIF(A6:O6,5)
B11 =COUNTIF(A6:O6,3)
A12 =(A11*100)/15
B12 =(B11*100)/15
A13=IF(B12>=73,"Red","Blue")

now based on the value generated in A13 I give the following 15 cells in row 8 a value between 1 and 0

A8 to O8 =IFERROR(IF(A13="Blue",1,0),"")


It's too complicated I'm sorry , I hope it makes any sense...
 
Last edited:
Upvote 0
Rick Rothstein said:
Actually, never mind on that... the following is minimally less efficient than what I was attempting to do, but it should work without any problems. It is still event code and is installed in the same way as the Change event code that I gave you earlier (which you should delete, by the way).
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim LastColumn As Long, JoinedText As String
  LastColumn = Cells(8, Columns.Count).End(xlToLeft).Column
  Application.EnableEvents = False
  If Len(Range("A21")) = 0 Then
    Range("A21").NumberFormat = "General"
  ElseIf LastColumn = 1 Then
    Range("A21").NumberFormat = "@"
    Range("A21").Value = Range("A8").Value
  Else
    Range("A21").NumberFormat = "@"
    Range("A21").Value = Join(Application.Index(Range("A8").Resize(, LastColumn).Value, 1, 0), "")
  End If
  Application.EnableEvents = True
End Sub
Click to expand...


I'm really sorry but I pasted in the above code but didn't get a result :S I don't want to be a pain but I really don't know what's going on..
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Concatenate data from two lists with inequal rows
Replies
8
Views
94
Cubist
C
R
  • Question
Conditional Concatenation
Replies
2
Views
339
RBusiness
R
C
  • Question
Compare cells in rows from 2 differents sheets before copy
2
Replies
15
Views
314
Tupe77
T
M
  • Solved
Converting numbers stored as text to regular numbers query (VBA)
Replies
4
Views
406
Alex Blakenburg
A
P
  • Solved
Concatenate Text To Populate 'Customer ID' Box
Replies
8
Views
348
Kerryx
K

Forum statistics

Threads
1,214,566
Messages
6,120,257
Members
448,952
Latest member
kjurney

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top