question on hiding rows

[Gary Keramidas]

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

 

[Franz Erhart]

Switch off ScreenUpdating and AutoCalculation to make it much faster.
I was able to hide 500 rows in no time - try it this way:

Sub HideRows()

Dim j As Integer

Application.ScreenUpdating = False ' makes it much faster
Application.Calculation = xlManual

For j = 6 To 510
Worksheets("ListX").Range("A" & j).EntireRow.Hidden = True
Next j

Application.ScreenUpdating = True
Application.Calculation = xlAutomatic


End Sub

 

[Gary Keramidas]

that's not the problem, those are already off.

 

[Bob Phillips]

Why don't you build up a range of the rows as you find a match, and the hide
the range at the end

With ws
For x = 5 To lastrow

If cell_meets_condition Then

If rng Is Nothing Then

Set rng = Rows(x)
Else

Set rng = Union(rng, Rows(x))
End If
End If
Nex t

If Not rng Is Nothing Then rng.Hidden = True
End With

or better, filter by your condition anhd delete visible rows.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Gary Keramidas]

thanks bob, i'll give those a try.

--


Gary

Why don't you build up a range of the rows as you find a match, and the hide
the range at the end

With ws
For x = 5 To lastrow

If cell_meets_condition Then

If rng Is Nothing Then

Set rng = Rows(x)
Else

Set rng = Union(rng, Rows(x))
End If
End If
Nex t

If Not rng Is Nothing Then rng.Hidden = True
End With

or better, filter by your condition anhd delete visible rows.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Geoff K]

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

 

[Geoff K]

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

 

[Gary Keramidas]

bob:

seems to work fine, just had to fix this:
If Not rng Is Nothing Then rng.EntireRow.Hidden = True

 

[Bob Phillips]

Gary,

That shouldn't have been necessary if you were adding whole rows to the
range as you encountered the condition .

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Gary Keramidas]

popped an error until i did that. unable to set the hidden property of the range
class.

here's what rng contains:
?rng.Address
$6:$16,$18:$39,$41:$68,$70:$95,$97:$100,$102:$107,$109:$144,$146:$170