Hide function taking a long time

[sweeneytime]

Hi guys,

Looking for some help please,

I have a report, 500 lines long, one column says yes or no, I loop through the column to the last cell. If it says no, then hide that row.

The below code works, but it takes about 2 or 3 minutes to run, would anybody know how I could shorten the running time please?

I took off screen updating but it didn't reduce the run time by much. And that was my "bright' idea! haha


Thanks,
Sweeneytime

Sub hide()
    
    Application.Calculation = xlManual
    Application.ScreenUpdating = False
   
    Dim LR As Long
    Dim myCell As Range
    Dim myRng As Range
    
    'Find last cell in AC
    LR = Range("AC" & Rows.Count).End(xlUp).Row
    
    'Set range
    Set myRng = Range("AC4:AC" & LR)
    
    'Loop through range
    For Each myCell In myRng.Cells
       
    ' If cell = No then hide row
    Select Case myCell.Value
        Case "No"
            myCell.Activate
            myCell.EntireRow.Select
            Selection.EntireRow.Hidden = True
                                  
        Case Else
                     
        
    End Select
    Next myCell
    
    
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
    
End Sub

 

[Sektor]

Sub HideRows()
    
    Application.Calculation = xlManual
    Application.ScreenUpdating = False
   
    Dim LR As Long
    Dim myRng As Range
    
    'Find last cell in AC
    LR = Range("AC" & Rows.Count).End(xlUp).Row
    
    'Set range
    Set myRng = Range("AC4:AC" & LR)
    
    ' Reset autofilter, if exists.
    With ActiveSheet
        If .AutoFilterMode Then .AutoFilter.Range.AutoFilter
    End With
    
    myRng.AutoFilter Field:=1, Criteria1:="No"
    
    On Error Resume Next
    myRng.SpecialCells(xlCellTypeVisible).EntireRow.Hidden = True
    
    If Err.Number <> 0 Then MsgBox "No matches found"
    
    ' Remove autofilter
    With ActiveSheet
        If .AutoFilterMode Then .AutoFilter.Range.AutoFilter
    End With
    
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
    
End Sub

 

[sweeneytime]

Thank you very much,

And you have killed 2 birds with one stone, solved my next problem too!

cheers

 

[Sektor]

Glad that helped you!