Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Hiding Lines

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Arizona
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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:-

    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 ]

  3. #3
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Arizona
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Arizona
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Mark.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •