Progress Bar in the Status Bar of Excel


Active Member
Sep 14, 2009
hi excel gurus,

I have a macro that takes quite some time to run, 3-4 minutes is it possible to have a % progress indicator in the status bar to let me know how much of the process has been completed so far?

Thank you :)

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
The short answer is yes.

you can use
 Application.StatusBar = intX & "% processed so far"

the longer answer is that is may be a bit more complicated.

Can you measure how far through the process you are (E.g. you know you are processing 50,000 records and you are currently on record 20,000 so you are 40% of the way)

Some processes cannot be interrupted by the status bar (e.g. if you have copied 50,000 formular1c1 cells and are waiting for Excel to resolve the formulas, there is no way to tell how many formulas have been resolved and how many there are to go.

Not all processes will take the same amount of time so calculating a time remaining could be difficult. If you are using finds, sumproducts, Vloolups etc on sorted data then records that appear at the top of the data take substantially less time to resolve than those at the bottom. E.g. I recently posted find functions in batches of 500. the first 500 took a fraction of a second to post and update. The 80th batch (40K lines down) were taking almost 3 seconds to post and update.
Upvote 0
you may also want to see if you can speed your code up.

Unecessary activates and selects slow things down. hiding or deleting lines one at a time is inefficient. It is better to flag the lines for deletion, then sort them and delete them as a single block.

some functions are really slow if used in bulk (sumproduct comes to mind :cool:) You may be better off creating temporary column values to find and match records and then clear up after yourself when finished

If you calulate values with formulas and no longer need the forumulas copy and pastespecial xlvalues so that reorganising the sheets does not cause formulas to re-calculate.

Using these techniques I got an application which was taking 90 minutes to complete to get to the end in under 8
Upvote 0
Hi excel gurus, i've taken your points across and i'm in the process of cleaning up coding, can anyone help advise me on how to improve the efficiency of the coding below:

    ActiveSheet.Range("A:L").AutoFilter Field:=11, Criteria1:="2"
    ActiveSheet.Range("A:L").AutoFilter Field:=8, Criteria1:="6"
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=COUNT(C[-12])"
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

i dont think there is a quicker method? :confused:

thank you
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back