将单元格中的换行符添加到 1000 行数据 [英] Add line breaks in cell to 1000 rows of data

问题描述

我目前正在使用以下代码为 C 列中的单元格数据添加换行符并将其复制到 K 列.我需要将换行符应用于一系列数据.我在 C 列中有 1000 行数据.任何帮助将不胜感激.

I am currently using the below code to add a line break to cell data in column C and copy it to column K. I need to apply line breaks to a range of data. I have 1000 rows of data in column C. Any help will be much appreciated.

Sub Macro
    Dim Stem As Variant
    Stem = ThisWorkbook.Worksheets ("Sheet1").Range("C2")
    Range ("K2").Select
    Range("K2").FormulaR1C1 = Stem & Chr(10) & ""
End Sub

谢谢

推荐答案

两件事:

• 尽早绑定变量总比晚绑定好(更好的内存管理，利用智能感知等)
• 通常最好尽可能避免使用选择".
• 您的 Stem 变量是一个对象(范围对象)，因此需要设置"

试试这个:

Sub Macro
    Dim WS As Worksheet
    Dim Stem As Range
    Dim R2 As Range
    Dim Rng as Range

    Set WS = ActiveWorkbook.Sheets("Sheet1")
    Set Stem = WS.Range("C2", Cells(WS.Range("C2").End(xlDown).Row, WS.Range("C2").Column))
    Set R2 = WS.Range("K2", Cells(Stem.End(xlDown).Row, WS.Range("K2").Column))

    R2.Value = Stem.Value
    'New Code
    For Each Rng In R2
        Rng.Value = Rng.Value & Chr(10) & ""
    Next Rng
    'Old Code: R2.End(xlDown) = R2.End(xlDown) & Chr(10) & ""

End Sub

这样做是首先设置您正在使用的工作表.然后，使用 Range(cell1, cell2) 格式设置工作范围(Stem).Cell1 我定义为C2".下一个表达式是使用 Cells() 函数.它相当于 VBA 中的C2"并按 Ctl+Down，然后查看您所在的行.

What this does is first sets the worksheet you're using. Then, you set your working range (Stem) using the Range(cell1, cell2) format. Cell1 I defined as "C2". The next expression there is using the Cells() function. It is the VBA equivalent of being in "C2" and hitting Ctl+Down, then seeing what row you're in.

然后，我以类似的方式设置您的目标范围 R2，但我使用 Stem 范围来确定它应该有多大.

Then, I set your destination range, R2, in a similar manner, but I used the Stem range to determine how large it should be.

最后，要获得准确的副本，您的目标范围必须与起始范围的大小相同..value 到 .value 表达式粘贴数据.然后，您的额外字符将添加到您的新数据字段中.

Finally, to get an accurate copy your destination range must be the same size as your from range. The .value to .value expression pastes the data. Then, your extra characters are added on to your new data field.

.End(xlDown) 的注意事项...如果您的数据中间有空白行，它将停在那里，而不是一直走到最后.希望这会有所帮助！

Something to keep in mind with .End(xlDown)... if you have blank rows in the middle of your data it will stop there, not go all the way down to the end. Hope this helps!

For Each 循环将遍历目标范围 R2 中的每个范围(即单元格)，并添加新字符.希望它更适合你.

The For Each loop will go through every range (i.e. cell) in your destination range, R2, and add your new characters. Hope that fits better for you.

这篇关于将单元格中的换行符添加到 1000 行数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！