Search code examples
excelvbacopylinebreak

How to copy multiple rows in Excel without a line break

I'm trying to figure out how to copy multiple rows without a line break from Excel to a text file via VBA. I found a code on this website from Peter (link) that helped me a lot but it only works for me when copying one cell (F1 for example):

Private Sub CommandButton3_Click()

Dim DataObj As New MSForms.DataObject

DataObj.SetText ActiveSheet.Range("F1")
DataObj.PutInClipboard


End Sub

I tried to modify the code above for copying multiple cells as follows:

Private Sub CommandButton3_Click()
    
Dim DataObj As New MSForms.DataObject

DataObj.SetText ActiveSheet.Range("F1:F10")
DataObj.PutInClipboard


End Sub

But I'm getting an error - Run-time error '13': type mismatch. Any idea how to do this?

Solution

  • The code fails because it expects information to be in Text format. You can modify .SetText line as below.

    DataObj.SetText Join(Application.Transpose(ActiveSheet.Range("F1:F10").Value))

    In principle, we are using Transpose to build a horizontal array and the use Join to build one string.

    Note: In your code, you have not used .value which still works as it is the implicitly used property. It is better to define the property explicitly i.e.

    DataObj.SetText ActiveSheet.Range("F1").Value