Hiding Lines

[jeffmfrank]

I found some good code to hide rows if a value is present in Column E of that row (which either contains "Hide" or "0"), but it's a loop and for 4200 rows it takes 10 minutes to work through. Any suggestions on how to speed this up?

I thought instead of evaluating each row and hiding, could I unhide all, evaluate each and select only, then hide all that had been selected? Anyone think this would be faster? What would that code look like? Thanks. Here's what I'm using now:

Private Sub Worksheet_Activate()
ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False
For Each c In Range("E1", Range("E4202").End(xlUp).Address)
Select Case c.Value
Case Is = "HIDE"
c.EntireRow.Hidden = True
Case Is = "0"
c.EntireRow.Hidden = False
End Select
Next c
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Thanks!

 

[dk]

Hi,

One of the simplest ways of speeding up such code is to turn off screen updating. I set up some sample data of 4200 rows and ran your macro. It took 15 seconds with screen updating turned on and 1 second with it turned off. Here is the modified code:-

Private Sub Worksheet_Activate()
Dim c As Range, t As Single


'ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False
Application.ScreenUpdating = False

For Each c In Range("E1", Range("E4202").End(xlUp).Address)
Select Case c.Value
Case Is = "HIDE"
c.EntireRow.Hidden = True
Case Is = "0"
c.EntireRow.Hidden = False
End Select
Next c


Application.ScreenUpdating = True
'ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

The quickest way to hide rows though is to use autofilter, either through code or manually.

HTH,
Dan
This message was edited by dk on 2002-05-10 10:25

 

[Mark O'Brien]

Something like this takes less than a second to run for 40 rows. It uses the "Find" method and no loops.

<pre>
Public Sub main()

Dim sText As String
Dim oRange As Range
Dim oTargetRange As Range
Dim sFirstRange As String
Dim sSelect As String

sText = "Hide"
Set oTargetRange = ActiveSheet.Range("E1", Range("E4202").End(xlUp).Address)

Set oRange = oTargetRange.Find(what:=sText, lookat:=xlWhole)

If Not oRange Is Nothing Then
sFirstRange = oRange.Address
Do
If oRange.Value = sText Then
sSelect = sSelect & "," & oRange.Address
End If
Set oRange = oTargetRange.FindNext(oRange)
Loop While Not oRange Is Nothing And sFirstRange <> oRange.Address
End If
ActiveSheet.Range(Right$(sSelect, Len(sSelect) - 1)).Select
Selection.EntireRow.Hidden = True
End Sub</pre>

I did this just for the cells that had the value "Hide" written in them. I couldn't see the point of this:
<pre>
Case Is = "0"
c.EntireRow.Hidden = False</pre>

If all of the rows are visible in the first place.

HTH

 

[jeffmfrank]

The autofilter works best. I didn't think about putting it into the code. Thanks!

 

[jeffmfrank]

Thanks Mark.