Range limit conundrum

Question

Hi,

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.

Answer 2

A faster option might be to use the SpecialCells property to find the blanks then hide the rows:

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.

Answer 3

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)

You didn't mention what 'quite a while' is, and what version of XL you are using...

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

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