如何将一个范围的SpecialCells分配到新范围？

Question

我知道我可以通过遍历第一个范围来完成此任务，但我很好奇我是否可以使用SpecialCells属性来完成此任务。

说我有一个空单元格名称之间的列：

A  B C 
Jon 
Jim 
Sally 

Jane 


Mary 

如果我想使用VBA在刚刚用过的细胞复制，我可以说

Range("A1:A8").SpecialCells(xlCellTypeConstants, xlTextValues).Copy 
Range("C1:C"&Range("A1:A8").SpecialCells(xlCellTypeConstants, xlTextValues).Count).PasteSpecial 

和结束与

A  B C 
Jon  Jon 
Jim  Jim 
Sally  Sally 
      Jane  
Jane  Mary 


Mary 

相反，我想能够做到这一点，而无需在任何地方粘贴范围。

我想要做的是有一个范围包含[Jon,Jim,Sally,Jane,Mary]，但如果我尝试Set rng = Range("A:A").SpecialCells(xlCellTypeConstants,xlTextValues)，我最终将空格作为范围的元素，或使用硬编码的单元格范围，一个在击中空间之前仅计入[Jon, Jim, Sally]。

我希望能够在代码中的其他地方使用范围，我认为SpecialCells是一个很好的紧凑方式，但是我唯一的选择是在循环中做它，并将单元格作为<> "" ？

Answer 1

考虑下面的代码

Dim r As Range 
Set r = Range("A:A").SpecialCells(xlCellTypeConstants, xlTextValues) 

Debug.Print r.Rows.Count, r.Cells.Count 
' returns:  3   5 

在上述唯一可靠的一条信息是r.Cells.Count。 Rows在第一张空白处被剪下。我想这会混淆整个粘贴过程。所以，你不能直接粘贴r到工作表。

你可以将它转移到一个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 

不需要检查空单元。

您也可以使用Chip Pearson的CollectionToArray procedure，这基本上是上述代码的更有趣的实现，可能稍作修改。

顺便说一下，检查<> ""将不会拒绝其值为空字符串""的单元格。如果您必须检查真空/“空白”单元，则IsEmpty更安全。

_{*在@Inssun上发布的信息用于在此上下文中创造“巴掌”。}

Answer 2

由于Range对象代表实际的细胞（A1，A2，A3，在这种情况下A5和A8），我不认为你可以在5个连续细胞紧凑型，而不会有任何粘贴。

但是，如果你需要在其上循环，你不需要使用的比较，使用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  

它可能不是很多，但它可以帮助你根据你想达到什么目的。

Answer 3

如果你真的想要使用specialcells，你需要为每个循环做一次，但是这里是如何做到这一点，没有变量数组或检查空单元格。请注意，我使用反向字典（请参阅下面的注释）将单元格存储为项目（而不是键），以便我可以利用.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 

但是有一种更快的方法可以做到这一点，以防您正在使用相当大的范围。使用字典对象是因为它能够吐出其中所有键/项目的数组（可以将其转置为一个范围）。集合不具备这种可靠性，但字典只允许每个关键字中的一个。解决方法？反过来使用字典，将您的值作为项目和钥匙计数器！

这里的（允许有受骗者）如何使用变量数组/词典的例子：

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 

这是比使用特殊的细胞（因为VBA处理工作，而咨询的Excel）快得多和你可以像使用粘贴一样使用转置，所以我更喜欢这种方法。

正如JFC指出的那样，使用字典对象似乎并没有太多好处，最大的原因是它很容易转换数组，并且可以水平转置它，这非常有趣。

Sheet1.Range(Cells(1, 3), Cells(1, dict.Count + 2)).Value = _ 
Application.Transpose(Application.Transpose(dict.items))