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.

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

I did this just for the cells that had the value "Hide" written in them. I couldn't see the point of this:

Case Is = "0"

c.EntireRow.Hidden = False

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.