How to copy multiple rows in Excel without a line break

Question

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?

ActiveSheet.Range("F1:F10") is a range not text, hence the error. Thus you need to convert the range to text before assigning to DataObj.SetText. — freeflow, Jan 23 '21 at 12:19

score 0 · Accepted Answer · answered Jan 23 '21 at 12:37

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

How to copy multiple rows in Excel without a line break

1 Answers1