Copy a cell text without any line break

slayer1957

New Member
Joined
Jan 9, 2017
Messages
28
Good day,

I need a Private sub when double clicked on a cell it must do the same as putting the cell to edit mode F2 and highlighting/selecting all text and copying it to clipboard. Currently it copies the text but when pasting in another program it pastes with multiple line breaks and i have to go and edit the whole document again. The code i have now is below.

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)On Error Resume Next
If Target.Value <> "" Then
Target.Copy
End If
Cancel = True
End Sub
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

slayer1957

New Member
Joined
Jan 9, 2017
Messages
28
Seems to be working with this code.

Code:
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next


Dim MyDataObj As New DataObject
Dim myStr As String


myStr = ActiveCell.Text
If Right(myStr, 1) = vbLf Then
myStr = Left(myStr, Len(myStr) - 1)
End If


MyDataObj.SetText myStr
MyDataObj.PutInClipboard
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,737
Messages
5,597,826
Members
414,180
Latest member
Sir Khaya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top