More Efficient code than this
In my view the fastest method is the one where you hide all the rows together
like that shown below.
On my computer if I hide each row separately, it took 20 seconds but using
the code below it took 1 second. The reading of the value of the cells takes
the longest amount of time in spreadsheet operations and hence I have
sometimes copied the entire range into memory at one go and then checked
values etc in that array.
Sub HideRows()
Dim i%, rng As Range
Set rng = Sheet1.Cells(1, 2)
For i = 2 To 10000
If Sheet1.Cells(i, 2) = "x" And Sheet1.Cells(i, 3) = "x" Then
Set rng = Union(rng, Sheet1.Cells(i, 2))
End If
Next i
rng.Rows.EntireRow.Hidden = True
End Sub
Alok Joshi
"thom hoyle" wrote:
Here is a piece of code I'm using to hide rows that have an "x" in two of the
columns. Is there a more efficient way to write this.
thanks
Sub HideRows()
Application.ScreenUpdating = False
Dim rng As Range
Dim i As Long
Set rng = Range("E5:E204")
i = 0 'set up a variable to see if range contains hidden rows
For Each c In rng
If c.EntireRow.Hidden = True Then
c.EntireRow.Hidden = False
i = 1
End If
Next
If i = 1 Then 'if any hidden rows unhide
Application.ScreenUpdating = True
Exit Sub
End If
For Each c In rng 'if no hidden rows unhidden then hide
If c.Value = "x" And c.Offset(0, 3).Value = "x" Then
c.EntireRow.Hidden = True
End If
Next
Application.ScreenUpdating = True
End Sub
|