excel - Range limit conundrum

Question

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

Answer 1

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.