Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concantenate All cells in Row left of current active cell--
need code to concantenate all cells in row left of current active cell.
Say I click cell h2, I want cells a, b, c, d, e, f, g, to populate cell h2. I want to use this as a button on my excel toolbar. If I can create a macro for it then it would be really handy. As sometimes I'm concantenating alot of data. Is there a quick way to do that in vba? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concantenate All cells in Row left of current active cell--
Here's my code... need some help tweaking it to detect
the amount of cells to the left. Sub ConcatColumns() Do While ActiveCell < "" 'Loops until the active cell is blank. 'The "&" must have a space on both sides or it will be 'treated as a variable type of long integer. ActiveCell.Offset(0, 1).FormulaR1C1 = _ ActiveCell.Offset(0, -3) & ActiveCell.Offset(0, -2) & ActiveCell.Offset(0, -1) & ActiveCell.Offset(0, 0) ActiveCell.Offset(1, 0).Select Loop End Sub "Benjamin" wrote: need code to concantenate all cells in row left of current active cell. Say I click cell h2, I want cells a, b, c, d, e, f, g, to populate cell h2. I want to use this as a button on my excel toolbar. If I can create a macro for it then it would be really handy. As sometimes I'm concantenating alot of data. Is there a quick way to do that in vba? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concantenate All cells in Row left of current active cell--
Please try this:
Sub ConCat() ' Dim i1 as long, str1 as string, started as boolean ' if activecell.column1 then activecell.value="" started=false for i1=1 to activecell.column - 1 str1=trim(cells(activecell.row,1)) if len(str1)0 then if started then activecell.value=activecell.value & ", " & str1 else activecell.value=str1 end if end if next i1 end sub It may easiest to go to tools/macro/macros and then select this macro and then use options to set a letter so that the macro will run by typing say Control+q. Or with the macro window open you can step whrough with F8 or run with F5. "Benjamin" wrote: need code to concantenate all cells in row left of current active cell. Say I click cell h2, I want cells a, b, c, d, e, f, g, to populate cell h2. I want to use this as a button on my excel toolbar. If I can create a macro for it then it would be really handy. As sometimes I'm concantenating alot of data. Is there a quick way to do that in vba? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concantenate All cells in Row left of current active cell--
Left a line out!
After "activecell.value=str1" please add a line: started = true Sorry about that. "Gleam" wrote: Please try this: Sub ConCat() ' Dim i1 as long, str1 as string, started as boolean ' if activecell.column1 then activecell.value="" started=false for i1=1 to activecell.column - 1 str1=trim(cells(activecell.row,1)) if len(str1)0 then if started then activecell.value=activecell.value & ", " & str1 else activecell.value=str1 end if end if next i1 end sub It may easiest to go to tools/macro/macros and then select this macro and then use options to set a letter so that the macro will run by typing say Control+q. Or with the macro window open you can step whrough with F8 or run with F5. "Benjamin" wrote: need code to concantenate all cells in row left of current active cell. Say I click cell h2, I want cells a, b, c, d, e, f, g, to populate cell h2. I want to use this as a button on my excel toolbar. If I can create a macro for it then it would be really handy. As sometimes I'm concantenating alot of data. Is there a quick way to do that in vba? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concantenate All cells in Row left of current active cell--
Here is a non-looping solution for you to try...
Sub ConCat() With WorksheetFunction ActiveCell.Value = Join(.Transpose(.Transpose(Range("A" & _ ActiveCell.Row, ActiveCell.Offset(0, -1)))), ", ") End With End Sub Note that I use a comma-space as a delimiter between the cell values (see end of the long code line)... you can change this as necessary. -- Rick (MVP - Excel) "Benjamin" wrote in message ... need code to concantenate all cells in row left of current active cell. Say I click cell h2, I want cells a, b, c, d, e, f, g, to populate cell h2. I want to use this as a button on my excel toolbar. If I can create a macro for it then it would be really handy. As sometimes I'm concantenating alot of data. Is there a quick way to do that in vba? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concantenate All cells in Row left of current active cell--
By the way Rick, Really awesome and short code!
Can you explain or give me link, to help me understand Join and Transpose. Works like a charm! Thanks again. "Rick Rothstein" wrote: Here is a non-looping solution for you to try... Sub ConCat() With WorksheetFunction ActiveCell.Value = Join(.Transpose(.Transpose(Range("A" & _ ActiveCell.Row, ActiveCell.Offset(0, -1)))), ", ") End With End Sub Note that I use a comma-space as a delimiter between the cell values (see end of the long code line)... you can change this as necessary. -- Rick (MVP - Excel) "Benjamin" wrote in message ... need code to concantenate all cells in row left of current active cell. Say I click cell h2, I want cells a, b, c, d, e, f, g, to populate cell h2. I want to use this as a button on my excel toolbar. If I can create a macro for it then it would be really handy. As sometimes I'm concantenating alot of data. Is there a quick way to do that in vba? . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concantenate All cells in Row left of current active cell--
What code to I remove to get rid of the "," delimiters?
Don't need them currently for my purposes. "Rick Rothstein" wrote: Here is a non-looping solution for you to try... Sub ConCat() With WorksheetFunction ActiveCell.Value = Join(.Transpose(.Transpose(Range("A" & _ ActiveCell.Row, ActiveCell.Offset(0, -1)))), ", ") End With End Sub Note that I use a comma-space as a delimiter between the cell values (see end of the long code line)... you can change this as necessary. -- Rick (MVP - Excel) "Benjamin" wrote in message ... need code to concantenate all cells in row left of current active cell. Say I click cell h2, I want cells a, b, c, d, e, f, g, to populate cell h2. I want to use this as a button on my excel toolbar. If I can create a macro for it then it would be really handy. As sometimes I'm concantenating alot of data. Is there a quick way to do that in vba? . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concantenate All cells in Row left of current active cell--
Sub Macro2()
' Keyboard Shortcut: Ctrl+Shift+C With WorksheetFunction ActiveCell.Value = Join(.Transpose(.Transpose(Range("A" & _ ActiveCell.row, ActiveCell.Offset(0, -1)))), "") End With End Sub Okay, I removed the delimiters.... with this code so forget that last request... just hacked away at it until I got it working. "Benjamin" wrote: By the way Rick, Really awesome and short code! Can you explain or give me link, to help me understand Join and Transpose. Works like a charm! Thanks again. "Rick Rothstein" wrote: Here is a non-looping solution for you to try... Sub ConCat() With WorksheetFunction ActiveCell.Value = Join(.Transpose(.Transpose(Range("A" & _ ActiveCell.Row, ActiveCell.Offset(0, -1)))), ", ") End With End Sub Note that I use a comma-space as a delimiter between the cell values (see end of the long code line)... you can change this as necessary. -- Rick (MVP - Excel) "Benjamin" wrote in message ... need code to concantenate all cells in row left of current active cell. Say I click cell h2, I want cells a, b, c, d, e, f, g, to populate cell h2. I want to use this as a button on my excel toolbar. If I can create a macro for it then it would be really handy. As sometimes I'm concantenating alot of data. Is there a quick way to do that in vba? . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concantenate All cells in Row left of current active cell--
Join is a VB function that takes a one-dimensional array of Strings (1st
argument) and concatenates its elements together using whatever text you specify as the delimiter (2nd argument). The Range property returns an array; but, unfortunately, that array is a two-dimensional one which the Join function cannot use. As for the worksheet Transpose function... I'm not entirely sure why it actually works, but it does, so I use it.<g All I know is that a **vertical** range of cells, when TRANSPOSE'd, becomes a one-dimensional array, but when a **horizontal** range of cells is TRANSPOSE'd, it remains a two-dimensional array. That is why your horizontal range had to be TRANSPOSE'd twice... the first time to make it a vertical (two-dimensional) array and the second time to make it a horizontal one-dimensional array. -- Rick (MVP - Excel) "Benjamin" wrote in message ... By the way Rick, Really awesome and short code! Can you explain or give me link, to help me understand Join and Transpose. Works like a charm! Thanks again. "Rick Rothstein" wrote: Here is a non-looping solution for you to try... Sub ConCat() With WorksheetFunction ActiveCell.Value = Join(.Transpose(.Transpose(Range("A" & _ ActiveCell.Row, ActiveCell.Offset(0, -1)))), ", ") End With End Sub Note that I use a comma-space as a delimiter between the cell values (see end of the long code line)... you can change this as necessary. -- Rick (MVP - Excel) "Benjamin" wrote in message ... need code to concantenate all cells in row left of current active cell. Say I click cell h2, I want cells a, b, c, d, e, f, g, to populate cell h2. I want to use this as a button on my excel toolbar. If I can create a macro for it then it would be really handy. As sometimes I'm concantenating alot of data. Is there a quick way to do that in vba? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
scroll so that the upper left cell in a range moves to the upper left of the active window | Excel Programming | |||
Name of the current pivottable from active cell | Excel Programming | |||
Moving left/right from active cell | Excel Programming | |||
Select column cells to the left/right of active selection - an example | Excel Programming | |||
Moving the Active cell to the left of the screen | Excel Programming |