XL VBA - Concatenate Range

[Dont Call me Betty]

Below code does it by using cell J1, then copies it.

How to do it without using any cell, straight to the clipboard?

Sub ConcatSelection()

Dim rng As Range
Dim i As String

For Each rng In Selection
i = i & rng & " "
Next rng

'Range("J1").Value = Trim(i)

Range("J1").Value = (i)
Range("J1").Copy
 
End Sub

 

[duggie33]

The post below may help you copy a cell's content to the Windows clipboard.

Copy from Excel and Paste in external app?

Good morning! Here's today's issue: I'm copying from a cell in Excel to another app. I'm getting a trailing carriage return when I paste in Notepad, Word, and Adobe Acrobat. Result: If I copy the text D10 and paste it in E10 I can get the value up in the Formula Bar. If I copy the...

www.mrexcel.com

Hope it helps,

Doug

 

[Dont Call me Betty]

The post below may help you copy a cell's content to the Windows clipboard.

Copy from Excel and Paste in external app?

Good morning! Here's today's issue: I'm copying from a cell in Excel to another app. I'm getting a trailing carriage return when I paste in Notepad, Word, and Adobe Acrobat. Result: If I copy the text D10 and paste it in E10 I can get the value up in the Formula Bar. If I copy the...

www.mrexcel.com

Hope it helps,

Doug

Not trying to use a cell, that's the solution I am trying to rise away from.

 

[duggie33]

Do you want "i" copied to the Windows clipboard?

If yes...

        With New DataObject
            .SetText i
            .PutInClipboard
        End With

 

[Dont Call me Betty]

Do you want "i" copied to the Windows clipboard?

If yes...

        With New DataObject
            .SetText i
            .PutInClipboard
        End With

With this code I get below error:

 

[duggie33]

In VBA editor, Tools, References, put a check by "Microsoft Forms 2.0 Object Library".

 

[Dont Call me Betty]

In VBA editor, Tools, References, put a check by "Microsoft Forms 2.0 Object Library".

Added that.
Expecting:

test two 123 d456

But get nothing...
Selecting A1:A4
Pasting into notepad gives nothing
Pasting into B5 gives those two little boxes with question marks

 

[duggie33]

I recall having a similar issue at some point. Do you have Windows File Explorer open? If yes, try closing that and seeing if you still get the two question marks? I am not 100% sure that is the cause...

 

[duggie33]

I tested this code...

Sub ConcatSelection()

    Dim rng As Range
    Dim i As String
  
    For Each rng In Selection
        i = i & rng & " "
    Next rng

    With New DataObject
        .SetText i
        .PutInClipboard
    End With

End Sub

The results were...

Just to make sure, have A1:A4 selected when you run the code. I hope it works for you...

Doug

 

[Dont Call me Betty]

I recall having a similar issue at some point. Do you have Windows File Explorer open? If yes, try closing that and seeing if you still get the two question marks? I am not 100% sure that is the cause...

With File Explorer closed, pasting is as expected in both places: NotePad and Excel
Then with File Explorer open, pasting as expected in NotePad, but same issue in Excel
Restarting Excel did not help.