VBA that hides rows with 0 takes FOREVER to run

Good evening all,

I've got a fairly simple code (see below), and it takes absolutely forever to fully run (I'm personally looking at ~4 minutes every time).

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Dim c As Range
For Each c In Range("J25:J4535")
If c.Value = 0 Then
Rows(c.Row).Hidden = True
Else
Rows(c.Row).Hidden = False

End If
Next c
End Sub

My ultimate purpose is I have other sheets that determine if the values in column G are 0, or are greater than 0, and if that value is 0, I want to hide the rows. The above code accomplishes the task, but takes a very long time.

Is there a better, more efficient way to go about this?

Hi

Having a bit of trouble replicating this, on my fairly slow laptop, processing the ~4,500 cells takes 1.04s. It goes up to 78s if i extend it to 64000 cells.

It kind of depends what's causing the slow behaviour. If you're hiding a large no of consecutive rows, you could get it to delay the hide operation until it comes across a row that doesn't need hiding.

For my example this was the case, so the following code (which groups the rows to be hidden) produced a massive improvement

Private Sub Worksheet_Activate()
t = Timer
Application.ScreenUpdating = False
Dim c As Range, firstRow As Long, alreadyHiding As Boolean
For Each c In Range("A1:A64535")
If c.Value = 0 Then
    'Rows(c.Row).Hidden = True
    If Not alreadyHiding Then
        firstRow = c.Row
        alreadyHiding = True
    End If
Else
    If alreadyHiding Then Rows(firstRow & ":" & c.Row - 1).Hidden = True
    Rows(c.Row).Hidden = False
    alreadyHiding = False
End If
Next c
If alreadyHiding Then Rows(firstRow & ":64545").Hidden = True

tt = Timer
Debug.Print tt - t
End Sub

If you are, for example, hiding every other row then you might need to look at building a selection and hiding them all at one, I think the main aim should be to use .hidden = true as little as possible

Cheers

Mat

Hi

Trying to remember what I meant myself! It would be complicated, but instead of hiding the range straight away, you could just select some rows (until you have a predefined no, say batches of 10) and then use .hidden=true in groups.

I think you could have a rolling selection string, which starts of as null, and add each row to be hidden, using something like

str=str&newrow&":"&newrow&","

such that you could use it in a rows() call (with the trailing comma removed). this would then let you use

range(str).areas.count

to see how many sets you'll be hiding, using

rows(left(str,len(str)-1)).hidden=true

would then hide the batch. If you tried to do it all at once, you'll likely hit some kind of limit for the number of areas in a range (although maybe not)

This is a bit of a brain dump, and is a bit muddled. Let me know if you'd like something a bit more complete.

cheers

Mat

Filter code would not take more than 10 seconds to run, but I am not a big fun of filtering. I am sure there are many people who can help you with.

hi have a look at this

http://www.excelforum.com/excel-prog...hide-rows.html

Mat,
I'm unfamiliar with the concepts you're proposing, and have spent the last week or so trying to fully understand it so I can utilize it, and I have unfortunately failed. Are you willing to elaborate a bit?