范围限制难题

本文介绍了范围限制难题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我可以通过 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.