[RESOLVED] Loop Tuning EXCEL

Hi,

What I want to do is to hide all row that are NOT bold or empty.
My range starts at row 12 and I don't know at witch row it finishes. I know that it finishes with the text GRAND TOTAL in column A and there is approx. 600 rows. I want this job to be performed when the button is clicked

So here is what I've done

VB Code:

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    ActiveSheet.Range("A12").Activate
    Do While (ActiveCell.Value <> "GRAND TOTAL")
        If (ActiveCell.Font.Bold = False Or ActiveCell.Value = "") Then
            ActiveCell.EntireRow.Hidden = True
        End If
        ActiveCell.Offset(1, 0).Activate
    Loop
    Application.ScreenUpdating = True
End Sub

This loop does all I want, but it takes 45 to 60 sec to accomplish. That is way too long.

Any idea?

Bill
Try turning calculation off before running the loop, then turn it back on.

humm, don't seem to speed it up :ehh:

Humm, I just ran this on a sheet with 23752 rows and 21 columns and it ran in under 1 second. There must be something else going on with your sheet.
(This is a stab in the dark) - Do you have any conditional formatting on the sheet?

Or do you have a worksheet_change or _sectionchange event? If so then disable events when looping.

No, but your stab in the dark gived me an hint.

I've tried the magic tricks of rebooting and it runned under 3 seconds

Thanks again for your help!!!

Enjoy windows and magic reboot...

it really only worked because I posted here....http://noteme.com/vb_extension/smilies/coolgleamA.gif

(oh the joys of MS...)