#1
|
|||
|
|||
VBA copy/paste
Hi all
I would like to know if can make a macro from this attachment example. Multiple columns numbers are copied into one cell. Thanks |
#2
|
||||
|
||||
It may not need a macro; if you have a version of Excel which has the TextJoin worksheet function you could have in G2:
=TEXTJOIN(" ",TRUE,A2:E4) or: =TEXTJOIN(" ",FALSE,A2:E4) depending upon how you want to treat empty cells. Then if you want to lose the formula you can copy, paste-special, Values afterwards. |
#3
|
|||
|
|||
Quote:
Thanks for the reply. |
#4
|
||||
|
||||
Well, let's give it a similar function. See attached where cells H2:H5 contain variants of the formula (just to demonstrate the differences when you supply different argument values - note that cell D3 is blank and cell C4 contains a zero).
The user-defined function has been stolen from https://www.ozgrid.com/forum/forum/h...325#post823325 and this could be tweaked to simplify its use for you. For information the code is: Code:
Function ConcatenateRange(Parts As Range, Separator As String, Blank As Integer, Zero As Integer) ' Build a single string from a passed range with a passed separator ' placed between each value. The separtaor can be more than 1 character ' If Blank = 0, cells = "" are ignored, else they are concatenated ' If Zero = 0, cells = 0 and <> "" are ignored, else they are concatenated Dim strAll As String, cel As Range, celCount As Integer strAll = "" celCount = 0 For Each cel In Parts.Cells If (Blank = 0 And cel.Value = "") Or (Zero = 0 And cel.Value = 0 And Len(cel) > 0) Then Else celCount = celCount + 1 strAll = strAll & Separator & cel.Value ' Removes separator placed before first concatenated cell If celCount = 1 Then strAll = Right(strAll, Len(strAll) - Len(Separator)) End If End If Next cel ConcatenateRange = strAll End Function |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
When I copy&paste a second copy appears that can't be edited | makeo22 | Word | 3 | 04-26-2017 07:09 PM |
Copy n Paste | seimeinokigen | Excel | 2 | 04-16-2016 09:31 AM |
Paste and Copy | Quadro | Excel | 4 | 07-15-2014 05:59 AM |
Paste Special: Copy and Paste Formatting Only? | tinfanide | Word | 6 | 03-06-2013 12:21 AM |
Can't copy paste | irenasobolewska | Office | 2 | 10-26-2012 05:09 PM |