我可以获取一系列单元格，删除重复项和空白单元格，然后复制到VBA Excel中已存在的表格中吗？

I am very new to VBA so forgive me if I'm doing this completely wrong. Basically, to give an overview of what I'm doing, I have a template that is filled out by a user. There are values spread out over the template that I need to add to a table but I don't want to add blanks or duplicates. I started by condensing them in a continuous range and I was able to figure out how to remove the duplicates, but I can't remove the one blank that is left. Also, I'm not sure the best way to add them to the table on another worksheet. This is what I have so far...

我对VBA很新，所以请原谅我，如果我这样做完全错了。基本上，为了概述我正在做的事情，我有一个由用户填写的模板。模板上有值，我需要添加到表中，但我不想添加空格或重复项。我开始在连续范围内冷凝它们，我能够找出如何删除重复项，但我无法删除剩下的一个空白。此外，我不确定将它们添加到另一个工作表上的表的最佳方法。这就是我到目前为止......

Sub UpdateKey()

 Range("P10:P36").Select

 Selection.Copy
 Selection.PasteSpecial Paste:=xlPasteValues

 Selection.RemoveDuplicates Columns:=1, Header:=xlNo

End Sub

Like I said, this always leaves me a blank right in the middle which I don't know how to get rid of, and I don't know where to go from here to add what's left to my table. Thanks for any help!

就像我说的那样，这总是让我在中间空白，我不知道如何摆脱，我不知道从这里去哪里添加剩下的东西到我的桌子。谢谢你的帮助！

1 个解决方案

#1

Try to use following code:

尝试使用以下代码：

Sub UpdateKey()
    With Range("P10:P36")
        .Value = .Value
        .RemoveDuplicates Columns:=1, Header:=xlNo
        On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).Delete xlShiftUp
        On Error GoTo 0
    End With
End Sub