A follow up:
On further experimentation I'm not sure what purpose the array is serving.
This seems quicker and from a range of 2000 rows with 1800 assorted
addresses for hiding it took 0.23 seconds to complete.
Perhaps a way of improving the performance further would be to reduce the
impact of the string length limitation by combining addresses viz
"A6:A25,A40,A67:A79" etc

Geoff K

Sub Testit3()

Dim x As Long
Dim lastrow As Long
Dim cAddr As String

Application.ScreenUpdating = False
Application.Calculation = xlManual

With Sheets(1)
lastrow = .Cells.Find("*", .Cells(1), xlFormulas, xlPart, xlByRows,
xlPrevious).Row
cAddr = ""
For x = 5 To lastrow
If .Range("G" & x).Value < 0 Or .Range("H" & x).Value < 0 Then
'do nothing
Else
cAddr = cAddr & "," & .Range("A" & x).Address(0, 0)
If Len(cAddr) 240 Then
cAddr = Right(cAddr, Len(cAddr) - 1)
.Range(cAddr).EntireRow.Hidden = True
cAddr = ""
End If
End If
Next
If Len(cAddr) 0 Then
cAddr = Right(cAddr, Len(cAddr) - 1)
.Range(cAddr).EntireRow.Hidden = True
End If
End With

Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

End Sub

"Geoff K" wrote:

Hi Gary
Very interesting. I found though that I had to make some amendments before
it would run and I have annotated the code to show this. I wonder did you
reset the array base to Option 1 perhaps?
I also found I had to reduce the frequency of hides from 50 to 40 if ranges
greater than a 1000 were used and is clearly something to do with the limit
of characters in the range as you had mentioned.
Looking at the code it would appear at first sight as though groups of rows
are hidden in multiples of the mod number but that does not seem to be the
case.
In the interest of performing less block hides I have included a debug.print
to show this.

To speed things up a little I have taken out the branch where the array is
incremented and delimiter added. This will of course add a delimeter at the
start of the string but that is removed just once before the hide statement.

Your code would also equally serve well to delete rows.

In my experiments I found the same character limitation on Bob Philips
suggestion though I have not taken that further.

I'd be grateful of your comments.

Geoff K

Sub TestHideRows()

Dim x As Long
Dim lastrow As Long
Dim arr()
Dim cAddr As String
Dim j As Long

'''as a given
'''Application.ScreenUpdating = False
'''Application.Calculation = xlManual

With Sheets(1)
lastrow = .Cells.Find("*", .Cells(1), xlFormulas, xlPart, xlByRows,
xlPrevious).Row
cAddr = ""
For x = 5 To lastrow
If .Range("G" & x).Value < 0 Or .Range("H" & x).Value < 0 Then
'do nothing
Else 'if both are zero
j = j + 1
ReDim Preserve arr(0 To x - 5) '<<< changed from 1 to 0
arr(x - 5) = .Range("A" & x).Address(0, 0)
cAddr = cAddr & "," & arr(x - 5)
If UBound(arr) Mod 40 = 0 Then '<<< changed from 50 to 40
cAddr = Right(cAddr, Len(cAddr) - 1)
Debug.Print j, cAddr
.Range(cAddr).EntireRow.Hidden = True
cAddr = ""
ReDim arr(0 To 1) '<<< changed from 1 to 0
End If
End If
Next
cAddr = Right(cAddr, Len(cAddr) - 1)
Debug.Print j, cAddr
.Range(cAddr).EntireRow.Hidden = True
End With

End Sub

"Gary Keramidas" wrote:

i have to loop through some rows and test to see which rows to hide. if i hide
the rows as i loop it takes 2 to 3 seconds to hide them. so i looked for a
better way.

as i loop, i create and array with a cell address of which rows i want to hide.
the only problem is, if the string is more than 256 characters, it won't work:
if this range contained more than 256 characters it wouldn't work
range("A6,A9,A12,A15").entirerow.hidden = true

so i wrote the code below and was wondering if there was a better way. this code
runs instantaneously, as opposed to 2 or 3 seconds to loop and hide each row 1
at a time.

it basically adds the cell address to a string until there are 50 address, then
it hides 50 rows at a time and resets the array. then what's less than 50 at the
end gets hidden when the loop exits.

data starts at row 5:

With ws
For x = 5 To lastrow
If .Range("G" & x).Value < 0 Or .Range("H" & x).Value < 0 _
Then
'do nothing
Else
ReDim Preserve arr(1 To x - 5)
arr(x - 5) = .Range("A" & x).Address(0, 0)
If cAddr = "" Then
cAddr = arr(x - 5)
Else
cAddr = cAddr & "," & arr(x - 5)
End If
If UBound(arr) Mod 50 = 0 Then
.Range(cAddr).EntireRow.Hidden = True
cAddr = ""
ReDim arr(1 To 1)
End If
End If
Next
ws.Range(cAddr).EntireRow.Hidden = True
End With
--


Gary