运行时错误'1004' - 删除行时对象"_Global"的方法'Range'失败

Question

我试图删除每奇数行在工作表一次，但它返回上述错误

代码:

For icount = endRow To 3 Step -2
    strDelete = strDelete & "," & icount & ":" & icount
Next icount
strDelete = Right(strDelete, Len(strDelete) - 1)
Range(strDelete).Delete shift:=xlUp        '<-- Error

这是strDelete所包含的

7565:7565, 7563:7563、7561:7561 7559:7559,7557:7557,7555:7555,7553:7553,7551:7551,7549:7549,7547:7547,7545:7545,7543:7543,

.

.

.

, 29:29, 27:27二五25,23:23,21:21,19:19,十七17,15:15,第13章13节,11:11,9:9,7:7,5,三3

一次删除的行数是否有限制?

Answer 1

可以删除的行数没有限制，但是Range的.Address属性可以返回的字符数有限制(或者在本例中通过其索引器)。不是构建字符串，而是使用Union:

构建一个实际范围。

Dim toDelete As Range
For i = endRow To 3 Step -2
    If toDelete Is Nothing Then
        Set toDelete = Rows(i)
    Else
        Set toDelete = Union(toDelete, Rows(i))
    End If
Next
toDelete.Delete shift:=xlUp

Answer 2

我想你一直在避免Union()方法，因为它很耗时

也许你一直都没有

无论如何，是耗时，当你有这么多的行要删除时，必须使用Range方法

正如已经指出的，限制是传递给Range()方法的字符串的长度，这似乎必须限制在大约250个字符(可能是256…)

那么让我们使用Range方法输入适当缩短的字符串

For icount = endRow To 3 Step -2
    strDelete = strDelete & "," & icount & ":" & icount
Next icount
DeleteAddress Right(strDelete, Len(strDelete) - 1)
Sub DeleteAddress(ByVal address As String)
    Dim arr As Variant
    Dim iArr As Long
    Dim partialAddress As String
    arr = Split(address, ",")
    iArr = LBound(arr)
    Do While iArr < UBound(arr)
        partialAddress = ""
        Do While Len(partialAddress & arr(iArr)) + 1 <= 250 And iArr < UBound(arr)
            partialAddress = partialAddress & arr(iArr) & ","
            iArr = iArr + 1
        Loop
        If Len(partialAddress & arr(iArr)) <= 250 Then
            partialAddress = partialAddress & arr(iArr)
            iArr = iArr + 1
        Else
            partialAddress = Left(partialAddress, Len(partialAddress) - 1)
        End If
        Range(partialAddress).Delete shift:=xlUp
    Loop
End Sub

比Union()的7k +行要快得多