Range limit conundrum

Solution 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.

Solution 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.

Solution 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

Solution 4:

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:

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

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.