vba 范围限制难题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1663264/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Range limit conundrum
提问by SnakeWasTheNameTheyGaveMe
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.
我可以通过 VBA 在一个范围内选择的内容有一些限制吗？基本上我发现，如果我要在循环中隐藏一整行，如果有很多行要隐藏，则需要很长时间。
ex) - Hide any row that doesn't have a value in column A
ex) - 隐藏 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.
更快速的方法是创建一个引用这些行中的每一行的单个范围，然后执行单个“.entirerow.hidden = true”语句。是的，我已经设置了 application.screenupdating = false 。
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.
第二个函数“dfdfd”只是用来举例说明它何时失败。要查看它何时起作用，只需创建一个包含 say - 5 项的新数组，然后尝试。有用。
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
Union（行高：0.109375 秒
Union（隐藏行）：0.625 秒
回答by jtolle
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.
我认为您在这里寻找的神奇函数是 Union()。它内置于 Excel VBA 中，因此请查看它的帮助。它做的正是你所期望的。
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.
我不知道您可以在单个 Range 中建立的区域数量的限制是什么（如果有），但它大于 600。我不知道选择或设置有什么（如果有）限制多区域范围的属性，但可能值得一试。
回答by dendarii
A faster option might be to use the SpecialCells property to find the blanks then hide the rows:
更快的选择可能是使用 SpecialCells 属性来查找空白然后隐藏行：
Sub HideRows()
Dim rng As Range
Set rng = ActiveSheet.Range("A1:A600")
Set rng = rng.SpecialCells(xlCellTypeBlanks)
rng.EntireRow.Hidden = True
End Sub
This will only work on cells within the UsedRange, I think.
我认为这仅适用于 UsedRange 内的单元格。
回答by caving
A minor speedup can be obtained if you set the RowHeight property to 0. On my system it goes about twice as fast (on 6000 iterations about 1.17 seconds versus 2.09 seconds)
如果将 RowHeight 属性设置为 0，可以获得较小的加速。在我的系统上，它大约快两倍（6000 次迭代大约 1.17 秒对 2.09 秒）
You didn't mention what 'quite a while' is, and what version of XL you are using...
您没有提到“相当长一段时间”是什么，以及您使用的是什么版本的 XL...
Your problem may be in part your row detect code that checks for a row you want to hide(?).
您的问题可能部分是您的行检测代码检查要隐藏的行（？）。
Here's my test code in XL 2003 (comment out one version then the other):
这是我在 XL 2003 中的测试代码（先注释掉一个版本，然后再注释掉另一个）：
Option Explicit
Public Sub test()
Dim i As Integer
Dim t As Long
t = Timer()
Application.ScreenUpdating = False
For i = 1 To 6000
With Range("A" & i)
'If .Value = vbEmpty Then .EntireRow.Hidden = True
If .Value = vbEmpty Then .RowHeight = 0
End With
Next
Application.ScreenUpdating = True
Debug.Print Timer() - t & " seconds"
End Sub
回答by blablubbb
There is a limit to the string length. I just encountered a similar problem and found that if the String Txt of Range(Txt) is larger then 255 characters my VBA throws an Error.eg. the code:
字符串长度有限制。我刚刚遇到了一个类似的问题，发现如果 Range(Txt) 的 String Txt 大于 255 个字符，我的 VBA 会抛出一个 Error.eg。编码：
Debug.Print sheet1.Range("R2300,T2300,V2300,R2261,T2261,V2261,R1958,T1958,V1958,R1751,T1751,V1751,R1544,T1544,V1544,R1285,T1285,V1285,R1225,T1225,V1225,R1193,T1193,V1193,R1089,T1089,V1089,R802,T802,V802,R535,T535,V535,R264,T264,V264,R205,T205,V205,R168,T168,V168,R135,T135,V135,R101").Areas.count
throws an error (256 characters in string) whereas the code
抛出错误（字符串中的 256 个字符）而代码
Debug.Print sheet1.Range("R230,T2300,V2300,R2261,T2261,V2261,R1958,T1958,V1958,R1751,T1751,V1751,R1544,T1544,V1544,R1285,T1285,V1285,R1225,T1225,V1225,R1193,T1193,V1193,R1089,T1089,V1089,R802,T802,V802,R535,T535,V535,R264,T264,V264,R205,T205,V205,R168,T168,V168,R135,T135,V135,R101").Areas.count
has 255 characters and prints out "46" without Error. The number of Areas is in both cases the same.
有 255 个字符并打印出“46”而没有错误。两种情况下的区域数量相同。
