范围极限难题

本文介绍了范围极限难题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 通过VBA可以在一个范围内选择一些限制？基本上我发现，如果我在一个循环中隐藏整行，如果有很多行要隐藏，需要相当长的一段时间。

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

对于i = 1到600 带范围（A& i）如果.value = vbEmpty然后.EntireRow.Hidden = True 结束下一个

更快捷的方式是使单个范围引用这些行，然后执行单个.entirerow.hidden = true语句。是的，我已经有application.screenupdating = false set。

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

以下代码声明一个函数，它接受标准数组的行号（如果数组在手之前制作）以及参数参数（如果是这样）你不想在手之前声明一个数组，行的列表很小）。然后创建一个在范围引用中使用的字符串。

函数GetRows（argsArray（）As Long，ParamArray args As Variant）As Range Dim rngs As String Dim r 对于每个r在argsArray rngs = rngs& ，& r& ：& r 下一个对于每个r在args rngs = rngs& ，& r& ：& r 下一个 rngs =右（rngs，Len（rngs） - 1）设置GetRows =范围（rngs） 结束函数函数dfdfd（） Dim selList（50）As Long，j As Long For i = 1 To 100 如果我的Mod 2 = 1然后 selList（j）= i j = j + 1 End If Next selList（50）= 101 GetRows（selList）.Select 结束函数

第二个函数dfdfd只是给出一个例子失败。要看看它的工作原理，只需制作一个新的数组，说 - 5个项目，并尝试。

最终（？）更新：

Option Explicit 公共子测试（） Dim i As Integer Dim t As Long Dim nRng As Range t = Timer（） Application.ScreenUpdating = False 设置nRng = [A1] 对于i = 1至6000 设置nRng = Union（nRng，Range（A& i））下一个 nRng.RowHeight = 0 'nRng.EntireRow.Hidden = true Application.ScreenUpdating = True Debug.PrintUnion（RowHeight）：& Timer（） - t& seconds'Debug.PrintUnion（EntireRow.Hidden）：& Timer（） - t& 秒 End Sub

结果：

联盟（行高：0.109375秒 联合（隐藏行）：0.625秒

解决方案

我认为你正在寻找的这个神奇的功能是Union（），它内置在Excel VBA中，所以看看它的帮助，它只是你所期望的。

循环遍历范围，而不是构建一个字符串，建立一个多区域范围，然后可以一次选择或设置整个属性。

我不知道（如果有的话）您可以在单个范围内建立的区域数量的限制，但是它大于600.我不知道（如果有的话）限制也可以选择或设置多区域范围的属性，但这可能值得一试。

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) - 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

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

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.

Final (?) update:

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

Results:

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

解决方案

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.

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.