Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-10-2018, 04:09 AM
Urraco Urraco is offline VBA copy/paste Windows 8 VBA copy/paste Office 2016
Advanced Beginner
VBA copy/paste
 
Join Date: Apr 2018
Posts: 30
Urraco is on a distinguished road
Default 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
Attached Images
File Type: jpg CropperCapture[26].jpg (63.9 KB, 34 views)
Reply With Quote
  #2  
Old 05-13-2018, 05:16 AM
p45cal's Avatar
p45cal p45cal is offline VBA copy/paste Windows 10 VBA copy/paste Office 2010 32bit
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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.
Reply With Quote
  #3  
Old 05-14-2018, 03:07 AM
Urraco Urraco is offline VBA copy/paste Windows 8 VBA copy/paste Office 2016
Advanced Beginner
VBA copy/paste
 
Join Date: Apr 2018
Posts: 30
Urraco is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
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.
TextJoin is a good solution but my excel does not have this function.
Thanks for the reply.
Reply With Quote
  #4  
Old 05-14-2018, 03:59 AM
p45cal's Avatar
p45cal p45cal is offline VBA copy/paste Windows 10 VBA copy/paste Office 2010 32bit
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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
Attached Images
File Type: jpg 2018-05-14_115303.jpg (29.3 KB, 18 views)
Attached Files
File Type: xlsm msofficeforums39033.xlsm (15.4 KB, 8 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA copy/paste When I copy&paste a second copy appears that can't be edited makeo22 Word 3 04-26-2017 07:09 PM
VBA copy/paste Copy n Paste seimeinokigen Excel 2 04-16-2016 09:31 AM
VBA copy/paste Paste and Copy Quadro Excel 4 07-15-2014 05:59 AM
VBA copy/paste 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

Other Forums: Access Forums

All times are GMT -7. The time now is 05:45 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft