[RESOLVED] Loop Tuning EXCEL

[billhuard]

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

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?

[DKenny]

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

[billhuard]

humm, don't seem to speed it up

[DKenny]

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.

[billhuard]

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...

[DKenny]

it really only worked because I posted here....

(oh the joys of MS...)