Userform VBA Progress Bar Associated with Workbook Completion

Question

I have a very large spreadsheet that currently has 129 tabs. In short, the below macro gets rid of all the array formula errors(#NUM) for each worksheet. This takes about 15-20 minutes but I want to know how close the macro is to finishing.

I designed a Userform progress bar without an issue and I have the code properly referencing the macro. It all works fine except the part that updates the actual Label in the Userform. How can I add code to define what percentage complete the macro is? I am assuming I need to use "current worksheets completed/total worksheet" but I'm extremely new to Userforms.

Sub DelNUM()
  Dim LR As Long
  Dim i As Long
  Dim sh As Worksheet

  For Each sh In Worksheets
    LR = sh.Range("B" & sh.Rows.Count).End(xlUp).Row
    For i = LR To 1 Step -1
        If IsError(sh.Range("B" & i)) Then sh.Rows(i).Delete
    Next i
  Next
End Sub

I already looked at the following link but it is for someone with more experience than me so I am having trouble following it: https://support.microsoft.com/en-us/help/211736/how-to-display-a-progress-bar-with-a-user-form-in-excel

Any and all help would be appreciated.

Thank you,

Sean

https://stackoverflow.com/questions/5181164/progress-bar-in-vba-excel — braX, Dec 27 '17 at 19:33
You will need to use `Me.Repaint` within the form somewhere where the form updates the progress. You can try `UserForm1.Repaint` right after your `If` statement, but I am unsure if that will update it — Maldred, Dec 27 '17 at 19:41
Wait - it takes *20 minutes* to run? I know you have a lot of sheets, but there is likely a quicker way...Perhaps filter the ranges out, delete those rows left? Perhaps take this over to [CodeReview](https://codereview.stackexchange.com/) if it works. Adding a percent bar is secondary, IMO, to getting this tweaked for efficiency. Currently going cell by cell is going to take, as you note, a long time. — BruceWayne, Dec 27 '17 at 20:05

Scott Holtzman · Accepted Answer · 2017-12-27T21:17:40.690

In support of @BruceWayne's comment, try this approach instead and see if you run-time decreases significantly:

Three important features in this code to save time are:

Turning off calculations (each row delete statement will trigger a re-calc of the workbook)
Use of SpecialCells to find all possible error cells in one shot!
Deleting rows 1 time per sheet. Each delete call takes up processing time.

Code below:

Option Explicit

Sub DelNUM()

    Dim LR As Long
    Dim i As Long
    Dim sh As Worksheet

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    For Each sh In Worksheets

        With sh

            LR = .Range("B" & sh.Rows.Count).End(xlUp).Row

            Dim formulaErrors As Range
            On Error Resume Next 'bc there may not be any error cells
            Set formulaErrors = .Range("B1:B" & LR).SpecialCells(xlCellTypeFormulas, xlErrors)
            On Error GoTo 0 'turn error catch back on

            If Not formulaErrors Is Nothing Then

                formulaErrors.EntireRow.Delete 'now we delete all error rows at once
                Set formulaErrors = Nothing 'reset for next loop

            End If

        End With

    Next

    Application.Calculation = xlCalculationAutomatic

End Sub

This way you may not need a progress bar :)

Holy great Scott Batman.....thank you guys. So it took less than 30 seconds. Nothing more humbling than this. — Sean Pakulski, Dec 28 '17 at 17:41
I do have a quick follow-up to this - The last worksheet uses a vlookup to find totals from the other worksheets. As a result of the array formulas, the last sheet pulls #NUM errors and with this upgraded code they get deleted. Is there a way to have the above skip the last worksheet in the workbook? — Sean Pakulski, Dec 28 '17 at 17:42
@SeanPakulski - Inside the `For` loop, before the `With sh` statement create an `IF sh.Name <> "LastSheetName" (replace with actual name) Then` and close out with `End If` in appropriate place. — Scott Holtzman, Dec 28 '17 at 18:19

Userform VBA Progress Bar Associated with Workbook Completion

1 Answers1