excel - 如何将一个范围的 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更安全。

_{*感谢 @Issun 在这种情况下创造了“slap”。}