范围限制难题 [英] Range limit conundrum

问题描述

我可以通过 VBA 在一个范围内选择的内容有一些限制吗?基本上我发现，如果我要在循环中隐藏一整行，如果有很多行要隐藏，则需要很长时间.

Is there some limit to what I can select in a range via VBA? Basically what I found is that if I were to hide an entire row while in a loop, it takes quite a while if there are lots of rows to hide.

ex) - 隐藏 A 列中没有值的任何行

ex) - Hide any row that doesn't have a value in column A

For i = 1 to 600
    With Range("A" & i)
        If .value = vbEmpty then .EntireRow.Hidden = True
    End With
Next

更快的方法是创建一个引用每一行的单一范围，然后执行一个.entirerow.hidden = true"语句.是的，我已经设置了 application.screenupdating = false.

The more speedy way of doing that is to make a single range that references each of those rows and then do a single ".entirerow.hidden = true" statement. And yes, I already have application.screenupdating = false set.

我遇到的问题是，如果范围的字符串引用太长，它就会失败.

The problem I'm encountering is that if the string reference for the range is too long, it just fails.

下面的代码声明了一个函数，它接受一个标准的行号数组(如果数组是事先创建的)，以及参数参数(如果你不想事先声明一个数组，以及行列表很小).然后它创建一个字符串，用于范围引用.

The following code declares a function which accepts both a standard array of row numbers (in case the array is made before hand), as well as parameter arguments (in case you don't want to declare an array before hand, and the list of rows is small). It then creates a string which is used in the range reference.

Function GetRows(argsArray() As Long, ParamArray args() As Variant) As Range

    Dim rngs As String
    Dim r

    For Each r In argsArray
        rngs = rngs & "," & r & ":" & r
    Next
    For Each r In args
        rngs = rngs & "," & r & ":" & r
    Next

    rngs = Right(rngs, Len(rngs) - 1)
    Set GetRows = Range(rngs)

End Function
Function dfdfd()

    Dim selList(50) As Long, j As Long
    For i = 1 To 100
        If i Mod 2 = 1 Then
            selList(j) = i
            j = j + 1
        End If
    Next
    selList(50) = 101
    GetRows(selList).Select

End Function

第二个函数dfdfd"只是用来举例说明它何时失败.要查看它何时起作用，只需创建一个包含 say - 5 项的新数组，然后尝试.它有效.

The 2nd function "dfdfd" is just used to give an example of when it fails. To see when it works, just make a new array with say - 5 items, and try that. It works.

最终 (?) 更新:

Option Explicit

Public Sub test()
    Dim i As Integer
    Dim t As Long
    Dim nRng As Range

    t = Timer()
    Application.ScreenUpdating = False
    Set nRng = [A1]
    For i = 1 To 6000
        Set nRng = Union(nRng, Range("A" & i))
    Next
    nRng.RowHeight = 0
    'nRng.EntireRow.Hidden = true
    Application.ScreenUpdating = True
    Debug.Print "Union (RowHeight): " & Timer() - t & " seconds"
    'Debug.Print "Union (EntireRow.Hidden): " & Timer() - t & " seconds"
End Sub

结果:

联合(行高:0.109375秒
Union(隐藏行):0.625 秒

Union (row height: 0.109375 seconds
Union (hidden row): 0.625 seconds

推荐答案

我认为您在这里寻找的神奇函数是 Union().它内置于 Excel VBA 中，因此请查看它的帮助.它完全符合您的预期.

I think the magical function you're looking for here is Union(). It's built into Excel VBA, so look at the help for it. It does just what you'd expect.

遍历您的范围，但不是构建字符串，而是构建多区域范围.然后，您可以一次为整个事物选择或设置属性.

Loop through your ranges, but instead of building a string, build up a multi-area Range. Then you can select or set properties on the whole thing at once.

我不知道您可以在单个 Range 中建立的区域数量的限制(如果有)是什么，但它大于 600.我不知道有什么(如果有)限制选择或设置多区域范围的属性，但可能值得一试.

I don't know what (if any) the limit on the number of areas you can build up in a single Range is, but it's bigger than 600. I don't know what (if any) limits there are on selecting or setting properties of a multi-area Range either, but it's probably worth a try.

这篇关于范围限制难题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！