Macro is slow when hiding rows when cell has specific value

[bisel]

I have a macro that runs when a sheet is activated. It is working OK ... but I need some help.

Here is the macro code for hiding rows when a cell in the row has a specific value.

:
:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each c In Range("compdetails_includes").Cells
    If c.Value = 0 Then
        c.EntireRow.Hidden = True
    Else: c.EntireRow.Hidden = False
    End If
  Next
:
:

In my case, the cell with have a value of either 0 or 1. If zero, then hide the entire row. I could easily change the value to True or False and then do a boolean check for value.

There are 2500 rows in the above range. This is working OK, but I am doing some mods and I find that if I copy a cell from another sheet into this sheet and do a paste special, copy picture link, instead of less than second to run the worksheet activate macro it takes over 7 seconds. So two questions for the group:

1. Why when I do a cell copy and then paste it as a picture link does that routine for hiding rows slow to a crawl?
2. Can anyone recommend a technique to speed up the hiding of an entire row based on the value in the cell?

Steve

 

[GWteB]

Why when I do a cell copy and then paste it as a picture link does that routine for hiding rows slow to a crawl?

No idea.

Can anyone recommend a technique to speed up the hiding of an entire row based on the value in the cell?

Try this ...

    Dim rHid As Range
    For Each c In Range("compdetails_includes").Cells
        If c.Value = 0 Then
            If rHid Is Nothing Then
                Set rHid = c
            Else
                Set rHid = Application.Union(rHid, c)
            End If
        End If
    Next
    Range("compdetails_includes").EntireRow.Hidden = False
    rHid.EntireRow.Hidden = True

 

[bisel]

No idea.



Try this ...

    Dim rHid As Range
    For Each c In Range("compdetails_includes").Cells
        If c.Value = 0 Then
            If rHid Is Nothing Then
                Set rHid = c
            Else
                Set rHid = Application.Union(rHid, c)
            End If
        End If
    Next
    Range("compdetails_includes").EntireRow.Hidden = False
    rHid.EntireRow.Hidden = True

Very nice. That addresses my issues. Works very rapidly.

Thanks,

Steve

 

[GWteB]

You are welcome and thanks for letting me know