我可以取一系列单元格，删除重复项和空白单元格，然后复制到 VBA Excel 中现有的表格中吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21146227/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can I take a range of cells, remove duplicates and blank cells, then copy into an already existing table in VBA Excel?
提问by novak
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!
就像我说的那样，这总是让我在中间留下一个空白，我不知道如何摆脱它，而且我不知道从哪里开始将剩下的东西添加到我的桌子上。谢谢你的帮助！
回答by Dmitry Pavliv
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
