在大型Excel文件的列中复制粘贴重复的值 [英] Copy pasting repeated values in a column of a large excel file

问题描述

这是我现在正在使用的工具：

我想要的是列B，C和& D复制到它下面的空白行，直到它到达一个新客户端，如下所示：

非常感谢任何和所有的帮助。

谢谢

解决方案

非VBA解决方案

使用数据选择整个范围。按 CTRL + G ，然后选择特殊...

选择空白并按确定

您将获得如下图所示的结果。 选定的单元格键入 = ，然后按↑键。按 CTRL + ENTER

结果：

现在，如果你希望您可以复制结果并使 PasteSpecial - >粘贴值

---

VBA解决方案：

  Sub test（）
 Dim rng As Range，ar As Range 
 On Error Resume Next 
设置rng =范围（A2：D20）SpecialCells（xlCellTypeBlanks）
错误GoTo 0 
 
如果不是rng是没有，然后
 rng.FormulaR1C1 == R [-1] C
 rng.Calculate 
对于每个ar在rng.Areas 
 ar.Value = ar.Value 
下一个
结束如果
 End Sub 
  

I'm stuck on a problem and would love some help.

Here is what I'm working with now:

What I'd like is for column B,C, & D to copy in the blank rows below it until it reaches a new client like so:

Any and all help is very much appreciated.

Thanks

解决方案

Non VBA solution:

Select entire range with your data. Press CTRL+G and select Special...

Choose Blanks and press OK

You would get result as in image below. With selected cells type = and press ↑ key. Press CTRL+ENTER

Result:

Now if you want you can copy result and make PasteSpecial-->Paste Values

---

VBA solution:

Sub test()
    Dim rng As Range, ar As Range
    On Error Resume Next
    Set rng = Range("A2:D20").SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0

    If Not rng Is Nothing Then
        rng.FormulaR1C1 = "=R[-1]C"
        rng.Calculate
        For Each ar In rng.Areas
            ar.Value = ar.Value
        Next
    End If
End Sub

这篇关于在大型Excel文件的列中复制粘贴重复的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！