Macro to hide rows running slowly

[onthegreen03]

I have a very simple macro which hides an entire row if the cell values in column AS (rows 1 through 140) are "Yes". The code I am using is below. The macro does the job but takes 5-6 seconds to hide the necessary rows. Given the fact the range is very small I am thinking there might be a better way to write this so it runs a little faster. Does anyone have a suggestion? Thanks in advance.

Sub Hide_Rows_Containing_Value()

Dim c As Range

For Each c In Range("AS1:AS140").Cells
If c.Value = "Yes" Then
c.EntireRow.Hidden = True

End If
Next c

End Sub

 

[Norie]

Try this.

Sub Hide_Rows_Containing_Value()
Dim rngToHide As Range
Dim c As Range

    For Each c In Range("AS1:AS140").Cells
        If c.Value = "Yes" Then
            If rngToHide Is Nothing Then
                Set rngToHide = c.EntireRow
            Else
                Set rngToHide = Union(rngToHide, c.EntireRow)
            End If
        End If
    Next c

    If Not rngToHide Is Nothing Then
        rngToHide.Hidden = True
    End If
End Sub

 

[MARK858]

Try using the Autofilter with does not equal yes as the criteria.

Range("AS1:AS140").Autofilter 1, "<>Yes"

Untested code as in my phone.

 

[onthegreen03]

Thanks both! I appreciate the alternate solutions.