范围对象可以容纳的单元格数量是否有限制

Question

我有一个工作表，大约有 300'000 行，其中一部分需要删除。

我用行号填充数组，并对数组中的行执行删除过程：

Sub Start()
'...
b = 0 
ReDim arr(b)
For i = LRow To 2 Step -1
    If .Cells(i, "L").Value = "" then
        ReDim Preserve arr(b)
        arr(b) = i
        b = b + 1
    End If
Next i
.Range("A" & Join(arr, ",A")).EntireRow.Delete
'...
End Sub

使用上面的代码，arr最终包含大约 68'000 行号。

在应该删除这些行的行上，我收到错误

对象"_Worksheet"的方法"范围"失败

当我尝试选择这些而不是删除它们时，也会发生这种情况。

从Join函数获取部分输出将按预期执行，例如：

.Range("A" & "2465,A2457,A2432,A2428,A2410,A2405,A2376,A2372,A2358,A2354").EntireRow.Delete

什么导致代码失败？我不知道的Range对象是否有限制？

Answer 1

这是一种方式：

Sub Start()
    '...
    Dim DeleteRange As Range
    For i = LRow To 2 Step -1
        If .Cells(i, "L").Value = "" Then
            If DeleteRange Is Nothing Then
                Set DeleteRange = .Cells(i, "A")
            Else
                Set DeleteRange = Union(DeleteRange, .Cells(i, "A"))
            End If
        End If
    Next i
    DeleteRange.EntireRow.Delete
    '...
End Sub

想尝试你的方式，它应该像你说的那样工作：

.Range("A" & "2465,A2457,A2432,A2428,A2410,A2405,A2376,A2372,A2358,A2354").EntireRow.Delete只要您的范围不超过 255 个字符。如果它不只是检查值是什么："A" & Join(arr, ",A")可能有错误或缺失。

编辑：另一种方法可以做到这一点

Sub Start()
    '...
    Dim arrData
    Dim j As Long
    b = 1
    arr = .UsedRange.Value
    ReDim arrData(1 To UBound(arr), 1 To UBound(arr, 2))
    For i = 1 To UBound(arr)
        If arr(i, 12) <> vbNullString Then
            For j = 1 To UBound(arrData, 2)
                arrData(b, j) = arr(i, j)
            Next j
            b = b + 1
        End If
    Next i
    .UsedRange = arrData
'...
End Sub

Answer 2

Range引用有 255 个字符的限制，您已经突飞猛进地超越了这个限制; 具体来说，

"A" & "2465,A2457,A2432,A2428,A2410,A2405,A2376,A2372,A2358,A2354..."

最多只能包含 255 个字符。

这可以通过一个简单的测试来验证：

Sub Test()
    Dim arr(1 To 40) As String
    Dim arr2(1 To 80) As String
    Dim i As Long
    For i = LBound(arr) To UBound(arr)
        arr(i) = CStr(i)
    Next i
    For i = LBound(arr2) To UBound(arr2)
        arr2(i) = CStr(i)
    Next i
    Debug.Print Len("A" & Join(arr, ",A")) ' returns 150
    Debug.Print Len("A" & Join(arr2, ",A")) ' returns 310
    Range("A" & Join(arr, ",A")).EntireRow.Delete ' works
    Range("A" & Join(arr2, ",A")).EntireRow.Delete ' fails
End Sub

如前所述，Union或阵列解决方案是替代方案。

Answer 3

使用联合函数组合数组的替代方法

通过 VBA 循环遍历范围非常耗时，使用 Union 通过EntireRow删除速度很快，因此将两者结合起来而不是收集所有剩余的列值似乎是一种好方法：

Sub DeleteEmptyRows()
With ThisWorkbook.Worksheets("Nonsens")
' assign column data to 2-dim array
  Dim v, lrow&: lrow = .Range("L" & Rows.Count).End(xlUp).Row
  v = .Range("L1:L" & lrow)                     ' start from top to get correct row numbers later
' check first empty cell
  Dim currRow&, delRng As Range
  For currRow = 2 To UBound(v)
      If v(currRow, 1) = vbNullString Then
           Set delRng = .Range("A" & currRow)
           Exit For
      End If
  Next currRow
' collect rest via UNION function
  For currRow = currRow To UBound(v)
      If v(currRow, 1) = vbNullString Then Set delRng = Union(delRng, .Range("A" & currRow))
  Next currRow
' delete entire rows in range set
  If Not delRng Is Nothing Then delRng.EntireRow.Delete
End With
End Sub