excel - 我可以只为包含计算数据的单元格构建数组，而忽略没有数据且仅包含基础公式的单元格吗？

Question

是否可以仅在单元格内形成包含文本的数组，而不是循环遍历嵌入公式的空单元格？

例如，我有一个收集两个维度的 UBound 的脚本，但它也会循环遍历没有计算数据的单元格(显示为空)。这会导致运行时间增加很多，因为我嵌入了跨越 1000 列和 62 行的公式。

我怎样才能告诉这个脚本只形成一个包含计算文本的单元格的数组，并忽略任何嵌入公式的可见空单元格？

Option Explicit

Sub Main()
  Dim wb As Workbook
  Dim Data, Last, JobFamily
  Dim sourcerow As Long, destcol As Long, sourcecol As Long, destrow As Long
  Dim Dest As Range
  Dim BASEPATH As String

  Dim sPath, sFile As String

  BASEPATH = "M:\Combine\"

  sPath = "M:\VBA\"
  sFile = sPath & "Book2.xlsx"

  Set wb = Workbooks.Open(sFile)
  Set Dest = wb.Sheets("Sheet1").Range("B1")
  With ThisWorkbook.Sheets("Profiles")
    Data = .Range("B1", .Cells(.Cells(.Rows.Count, 2).End(xlUp).Row, .Columns.Count).End(xlToLeft))
  End With
  wb.Activate
  Application.ScreenUpdating = False

  For sourcecol = 1 To UBound(Data, 2)
    If Data(1, sourcecol) <> Last Then
      If sourcecol > 1 Then
        Dest.Select
          wb.SaveCopyAs BASEPATH & _
          ValidFileName(Last & ".xlsx")
      End If

      Dest.Resize(, Columns.Count - Dest.Column).EntireColumn.ClearContents
      Last = Data(1, sourcecol)
      destcol = 0
    End If

    destrow = 0
    For sourcerow = 1 To UBound(Data)
      Dest.Offset(destrow, destcol) = Data(sourcerow, sourcecol)
      destrow = destrow + 1
    Next

    destcol = destcol + 1
  Next
End Sub

Answer 1

是的，您可以使用 Range 对象的 SpecialCells 方法。这是一个小型的独立演示。更多信息在这里SpecialCells

Private Sub BuildConstantRange()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        .Cells(1, 1).Value = "1"
        .Cells(2, 1).Value = "2"
        .Cells(3, 1).Value = "=if(1=1,1,0)"
        .Cells(4, 1).Value = "=if(1=1,1,0)"
        .Cells(5, 1).Value = "3"

        Dim rng As Range: Set rng = .Range("A1:A5").SpecialCells(xlCellTypeConstants)
        Dim cell As Range

        For Each cell In rng
            Debug.Print cell.Address
        Next
    End With

End Sub

输出是:

$A$1
$A$2
$A$5

哪些单元格没有公式。