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]

I do not know of any fix for it. It may be a known Windows bug...I know it bugs me Hopefully, you don't need the File Explorer open.

Edit: there might be a workaround here...https://www.reddit.com/r/vba/comments/i0pnmk/why_am_i_getting_two_boxes_with_question_marks_on/?rdt=48314

Doug

 

[Dont Call me Betty]

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...
View attachment 99877

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

Doug

Of course those are selected. And the code is the same. But, guessing you don't have the File Explorer open when it is a success?
Need to have it work even when File Explorer is open.

 

[rlv01]

There is an issue with using the DataObject to paste to the Windows clipboard. It does not work if there is more than one instance of Windows Explorer open. Try this alternate method.

Sub ConcatSelection()
    Dim rng As Range
    Dim i As String
    Dim CopySuccess As Boolean

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

    ClearClipBoardText
    CopySuccess = SetClipBoardText(i)
End Sub

Private Function ClearClipBoardText() As Boolean
    ClearClipBoardText = CreateObject("htmlfile").ParentWindow.ClipboardData.clearData("Text")
End Function

Private Function SetClipBoardText(ByVal Text As Variant) As Boolean
    SetClipBoardText = CreateObject("htmlfile").ParentWindow.ClipboardData.SetData("Text", Text)
End Function

 

[duggie33]

Works great rlv01! Thanks.

Doug