运行时错误'1004'-删除行时对象'_Global'的方法'Range'失败 [英] Run-time error '1004' - Method 'Range' of object'_Global' failed while deleting rows

问题描述

我正在尝试一次删除工作表中的所有奇数行，但它会返回上述错误

I am trying to delete every odd row in a worksheet at once but it returns the above error

代码:

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，

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:25,23:23,21:21,19:19,17:17,15:15,13:13,11:11,9:9,7:7,5:5,3:3

,29:29,27:27,25:25,23:23,21:21,19:19,17:17,15:15,13:13,11:11,9:9,7:7,5:5,3:3

我们可以一次删除的行数是否有限制?

Are there any limitations to number of rows we can delete at once ?

推荐答案

我认为您一直在避免使用 Union()方法，因为它很耗时

I think you've been avoiding Union() approach since it's time consuming

也许您还没来过

无论如何都是耗时的，并且当要删除大量行时，必须使用 Range 方法

Anyhow it is time consuming and when you have such a great amount of rows to delete you must use the Range approach

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

As already pointed out, the limit is on the length of the string to be passed to Range() method, which seems must be limited to some 250 characters (may be 256...)

因此，让我们使用Range方法来正确输入缩短的字符串

So let's use the Range approach feeding properly shortened strings

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行以上要快得多

which is much much much faster than the Union() approach with 7k plus rows

这篇关于运行时错误'1004'-删除行时对象'_Global'的方法'Range'失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！