复制单元格时，MS Excel会添加换行符 [英] MS Excel adds line break when copying a cell

问题描述

当您复制单元格（不从单元格中选择文本）并粘贴到任何编辑器时，MS Excel 2010或2007或任何版本的任何版本将添加换行符，并将光标发送到下一行。令人厌烦的是将光标向上移动到最后一行。

将光标返回到最后一行不会有太多的工作，但如果您不得不像程序员一样保持这样做，我感觉真的很糟糕。任何建议，如果有一个设置在excel停止这个？

我试着看网上没有找到任何东西，也查找了excel的选项，没有找到任何东西。

解决方案

您可以创建自己的复制例程，不存储整个单元格，而只存储在剪贴板中的值： / p>

  Sub OwnCopy（）
 Dim DataObj作为新的MSForms.DataObject 
 
 DataObj.SetText ActiveCell .Value'根据你想要的，你也可以使用.Formula here 
 DataObj.PutInClipboard 
 End Sub 
  

为了能够编译宏，您需要将Microsoft Forms 2.0对象库作为参考（在Visual Basic编辑器中，转到工具 - >参考）。

现在，您可以将此宏分配给另一个快捷方式（例如 Ctrl - Shift - C ），使用默认运行宏对话框 - 甚至通过执行 Application.OnKey^ c，OwnCopy Ctrl - c >。但是，我不建议覆盖 Ctrl - c ，因为您最有可能希望使用其他任何其他情况下复制的其他信息，而不是粘贴要编辑。

为了使这个永久性，只需将宏存储在您的个人工作簿中。

如果您想要一个更复杂的复制例程，也可以处理多个单元格/选择区域，请使用以下代码：

  Sub OwnCopy2（）
 Dim DataObj As New MSForms.DataObject 
 Dim lngRow As Long 
 Dim intCol As Integer 
 Dim c As Range 
 Dim strClip As String 
 
 Const cStrNextCol As String = vbTab 
 Const cStrNextRow As String = vbCrLf 
 
带有选择
如果不是TypeOf选择是范围然后
错误恢复下一步
。复制
退出子
结束如果
 
如果.Areas.Count = 1然后
对于lngRow = 1 T o .Rows.Count 
对于intCol = 1 To .Columns.Count 
 strClip = strClip& _ 
选择（lngRow，intCol）.Value& cStrNextCol 
下一个intCol 
 strClip = Left（strClip，Len（strClip） -  Len（cStrNextCol））_ 
& cStrNextRow 
下一页lngRow 
 Else 
对于每个c在.Cells 
 strClip = strClip& c.Value& vbCrLf 
 Next c 
 End If 
 strClip = Left（strClip，Len（strClip） -  Len（cStrNextRow））
 DataObj.SetText strClip 
 DataObj.PutInClipboard 
结束
 End Sub 
  

MS Excel 2010 or 2007 or any version for that matter when you copy a cell(not selecting text from the cell) and pasting to any editor adds a line break and sends the cursor to next line. Its annoying to move up the cursor back to last line.

It doesnt look much work to get the cursor back to last line but if you have to keep doing this a lot of times as a programmer i feel really bad. Any suggestions if there is a setting in excel to stop this?

I tried looking online didnt find any thing and also looked up the options of excel, didnt find anything.

解决方案

You can create your own copy routine, that does not store the whole cell, but only its value in the clipboard:

Sub OwnCopy()
    Dim DataObj As New MSForms.DataObject

    DataObj.SetText ActiveCell.Value 'depending what you want, you could also use .Formula here
    DataObj.PutInClipboard
End Sub

To be able to compile the macro, you need to include "Microsoft Forms 2.0 Object Library" as a reference (in the Visual Basic editor, goto Tools->References)..

You can now either assign this macro to another shortcut (e.g. Ctrl-Shift-C) using the default run macro dialog - or even overwrite Ctrl-c by executing Application.OnKey "^c", "OwnCopy". However, I'd not recommend overwriting Ctrl-c as you'll most likely want to use all the other information that usually is copied with it in any other case than pasting to an editor.

To make this permanent, just store the macro in your Personal Workbook.

In case you want a more sophisticated copy routine that can also handle multiple cells/selection areas, use this code:

Sub OwnCopy2()
    Dim DataObj As New MSForms.DataObject
    Dim lngRow As Long
    Dim intCol As Integer
    Dim c As Range
    Dim strClip As String

    Const cStrNextCol As String = vbTab
    Const cStrNextRow As String = vbCrLf

    With Selection
        If Not TypeOf Selection Is Range Then
            On Error Resume Next
            .Copy
            Exit Sub
        End If

        If .Areas.Count = 1 Then
            For lngRow = 1 To .Rows.Count
                For intCol = 1 To .Columns.Count
                    strClip = strClip & _
                        Selection(lngRow, intCol).Value & cStrNextCol
                Next intCol
                strClip = Left(strClip, Len(strClip) - Len(cStrNextCol)) _
                    & cStrNextRow
            Next lngRow
        Else
            For Each c In .Cells
                strClip = strClip & c.Value & vbCrLf
            Next c
        End If
        strClip = Left(strClip, Len(strClip) - Len(cStrNextRow))
        DataObj.SetText strClip
        DataObj.PutInClipboard
    End With
End Sub

这篇关于复制单元格时，MS Excel会添加换行符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！