vba 如何将一个范围的 SpecialCell 分配给新范围？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7156526/
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
How can I assign the SpecialCells of one range to a new range?
提问by jonsca
I know that I can accomplish this via iterating over the first range, but I'm curious to see if I can accomplish it using the SpecialCellsproperty.
我知道我可以通过迭代第一个范围来完成这个，但我很好奇我是否可以使用该SpecialCells属性来完成它。
Say I have a column of names with empty cells in between:
假设我有一列名称，中间有空单元格：
A B C
Jon
Jim
Sally
Jane
Mary
If I want to use VBA to copy over just the used cells, I can say
如果我想使用 VBA 只复制使用过的单元格，我可以说
Range("A1:A8").SpecialCells(xlCellTypeConstants, xlTextValues).Copy
Range("C1:C"&Range("A1:A8").SpecialCells(xlCellTypeConstants, xlTextValues).Count).PasteSpecial
and end up with
并以
A B C
Jon Jon
Jim Jim
Sally Sally
Jane
Jane Mary
Mary
Instead, I'd like to be able to do this without having to paste a range anywhere.
相反，我希望能够做到这一点，而不必在任何地方粘贴范围。
What I want to be able to do is have a range containing [Jon,Jim,Sally,Jane,Mary], but if I try Set rng = Range("A:A").SpecialCells(xlCellTypeConstants,xlTextValues), I either end up with the spaces as elements of the range, or using a hard-coded range of cells, with one that counts only [Jon, Jim, Sally]before it hits the space.
我希望能够做的是有一个包含 的范围[Jon,Jim,Sally,Jane,Mary]，但如果我尝试Set rng = Range("A:A").SpecialCells(xlCellTypeConstants,xlTextValues)，我要么以空格作为范围的元素，要么使用硬编码的单元格范围，其中一个仅[Jon, Jim, Sally]在命中之前计数空间。
I'd like to be able to use the range elsewhere in the code, and I think the SpecialCells is a nice compact way of doing it, but is my only alternative to do it in a loop and compare cells as <> ""?
我希望能够在代码中的其他地方使用范围，并且我认为 SpecialCells 是一种很好的紧凑方式，但是我唯一的替代方法是在循环中进行并将单元格比较为<> ""?
回答by Jean-Fran?ois Corbett
Consider the following code
考虑以下代码
Dim r As Range
Set r = Range("A:A").SpecialCells(xlCellTypeConstants, xlTextValues)
Debug.Print r.Rows.Count, r.Cells.Count
' returns: 3 5
The only reliable piece of information in the above is r.Cells.Count. The Rowsget cut at the first blank. I imagine this confuses the whole pasting process. So, you can't paste rdirectly to the worksheet.
上面唯一可靠的信息是r.Cells.Count。在Rows得到削减在第一个空白。我想这会混淆整个粘贴过程。因此，您不能r直接粘贴到工作表。
You could transfer it to a Variant array, and then slap* that onto the sheet. But how to do this? Well, r.Cellsis akin to a collection. Perhaps convert it to an array like this:
您可以将其传输到 Variant 数组，然后将其拍打* 到工作表上。但是如何做到这一点呢？嗯，r.Cells类似于一个集合。也许将其转换为这样的数组：
Dim i As Long
Dim c As Range
Dim v As Variant
ReDim v(1 To r.Cells.Count, 1 To 1)
i = 0
For Each c In r
i = i + 1
v(i, 1) = c
Next c
Range("B1").Resize(UBound(v,1),UBound(v,2)) = v
No need to check for empty cells.
无需检查空单元格。
You could also use Chip Pearson's CollectionToArrayprocedure, which is basically a fancier implementation of the above code, maybe with a bit of modification.
您也可以使用 Chip Pearson 的CollectionToArray程序，它基本上是上述代码的更高级实现，可能需要稍作修改。
By the way, checking for <> ""will not reject cells whose value is an empty string "". If you must check for truly empty/"blank" cells, then IsEmptyis safer.
顺便说一下，检查<> ""不会拒绝值为空字符串的单元格""。如果您必须检查真正的空/“空白”单元格，那么IsEmpty更安全。
*Credits to @Issun for coining "slap" in this context.
*感谢@Issun 在这种情况下创造了“巴掌”。
回答by aevanko
If you really want to just use the specialcells, you'll need to do a for each loop, but here's how to do it without a variant array or checking for empty cell. Note that I am using a dictionary in reverse (see notes below) to store cells as items (not keys) so I can utilize the .Items method that spits out an array of all items in the dictionary.
如果你真的只想使用特殊单元格，你需要为每个循环做一个，但这里是如何在没有变体数组或检查空单元格的情况下做到这一点。请注意，我反向使用字典（请参阅下面的注释）将单元格存储为项目（而不是键），因此我可以利用 .Items 方法输出字典中所有项目的数组。
Sub test()
Dim cell As Range
Dim i As Long
Dim dict As Object
Set dict = CreateObject("scripting.dictionary")
For Each cell In Range("A1:A10").SpecialCells(xlCellTypeConstants)
dict.Add i, cell.Value
i = i + 1
Next
Sheet1.Range("c1").Resize(dict.Count).Value = _
Application.Transpose(dict.items)
End Sub
But there is a faster way to do this, in case you are working with a fairly big range. Use the dictionary object since it has the ability to spit out an array of all the keys/items inside it (which you can transpose to a range). Collections do not have this abliity, but dictionaries only allow 1 of each key. The work around? Use the dictionary in reverse, placing your values as items and a counter for keys!
但是，如果您正在使用相当大的范围，则有一种更快的方法可以做到这一点。使用字典对象，因为它能够吐出其中包含所有键/项目的数组（您可以将其转换为范围）。集合没有这种能力，但字典只允许每个键 1 个。周围的工作？反过来使用字典，将您的值作为项目和键的计数器！
Here's an example of how to use the variant array/dictionary (with dupes allowed):
以下是如何使用变体数组/字典的示例（允许欺骗）：
Sub test()
Dim vArray As Variant
Dim i As Long, j As Long, k As Long
Dim dict As Object
Set dict = CreateObject("scripting.dictionary")
vArray = Range("A1:A10").Value
For i = 1 To UBound(vArray, 1)
For j = 1 To UBound(vArray, 2)
If Len(vArray(i, j)) <> 0 Then
dict.Add k, vArray(i, j)
k = k + 1
End If
Next
Next
Sheet1.Range("c1").Resize(dict.Count).Value = _
Application.Transpose(dict.items)
End Sub
This is much fasterthan using special cells (since VBA handles the work without consulting Excel) and you can use transpose as if it were paste, so I prefer this method.
这比使用特殊单元格快得多（因为 VBA 无需咨询 Excel 即可处理工作）并且您可以像粘贴一样使用转置，所以我更喜欢这种方法。
As JFC has noted, using a dictionary object doesn't really seem to have a lot of benifits, the biggest reason is that it's easy to transpose the array plus you can transpose it horizontally as well, which is very fun.
正如 JFC 所指出的，使用字典对象似乎并没有很多好处，最大的原因是它很容易转置数组，而且你也可以水平转置它，这很有趣。
Sheet1.Range(Cells(1, 3), Cells(1, dict.Count + 2)).Value = _
Application.Transpose(Application.Transpose(dict.items))
回答by Joubarc
Since the Range object represents actual cells (A1,A2,A3,A5and A8in this case), I don't think you can compact in a 5-consecutive-cells Range without pasting it anywhere.
由于 Range 对象代表实际单元格（A1, A2, A3,A5和A8在这种情况下），我认为您不能在不将其粘贴到任何地方的情况下压缩 5 个连续单元格范围。
However, if you need to to loop on it, you don't need to use a comparison, using For Eachwill skip the blanks:
但是，如果您需要对其进行循环，则不需要使用比较，使用For Each将跳过空格：
Set Rng = Range("A1:A8").SpecialCells(xlCellTypeConstants, xlTextValues)
Print Rng.count
5
For Each cell in Rng: Print cell: Next cell
Jon?
Jim?
Sally?
Jane?
Mary?
It's probably not much, but it may help you depending on what you want to achieve.
它可能并不多，但它可能会帮助您，具体取决于您想要实现的目标。
