Adding progress bar to code

Hi -
I'm very new to VBA and wanted to add a progress bar to my script below. How would I go about adding one so I know how much of the job is completed

?

Option Explicit


Sub DeleteNARows()
    Dim r As Long
    Dim iCol As Long
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .DisplayAlerts = False
        For iCol = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
            For r = Cells(Rows.Count, iCol).End(xlUp).Row To 1 Step -1
                If Application.WorksheetFunction.IsNA(Cells(r, iCol)) Then Rows(r).Delete
            Next r
        Next iCol
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
End Sub

Re: Adding progress bar to code

Hi Smallman -
Yes, I am trying to delete all rows with a N/A in it. I used your script but it didn't work...It just filtered, but deleted everything else. I want to delete the N/As but keep everything else...

Quote from Smallman;705631

Hi

I would have to agree with Trunten. Progress bars add time to code. I think if you step back one step and say why am I wanting to add a progress bar. My first thought was the code is taking a long time that is why he wants a bar. So lets look at your code. It looks like you are wanting to delete cells with NA#.

Do it all at once. It saves so much time you will gasp.

This

Code

Sub GetRid()
Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, "#N/A"
Range("A2", Range("A" & Rows.Count).End(xlUp)).EntireRow.Delete
[a1].AutoFilter
End Sub

Take care

Smallman

Display More

Re: Adding progress bar to code

Thanks Trunten - This is helpful to see the code.

Quote from trunten;705629

Short answer is don't, it adds unnecessary time to the execution of your code. VBA is only single threaded so you have to waste time updating a progress bar.
If you're sure you want one though the quickest way to do it is through the status bar otherwise it gets a bit more complex.

Code

Option Explicit 


Sub DeleteNARows() 
    Dim r As Long 
    Dim iCol As Long 
    Dim i as long


    With Application 
        .Calculation = xlCalculationManual 
        .ScreenUpdating = False 
        .DisplayAlerts = False 
        For iCol = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
            i = Cells(Rows.Count, iCol).End(xlUp).Row
            For r = i To 1 Step -1 
                If Application.WorksheetFunction.IsNA(Cells(r, iCol)) Then Rows(r).Delete 
                Application.StatusBar = cint(((i - r)/i) * 100) & "% complete"
            Next r 
        Next iCol 
        .StatusBar = FALSE
        .Calculation = xlCalculationAutomatic 
        .ScreenUpdating = True 
        .DisplayAlerts = True 
    End With 
End Sub

Display More

Re: Adding progress bar to code

Hi Smallman -

I appreciate your help on this. This helps immensely. Thanks

Re: Adding progress bar to code

Hi Smallman - quick question for you. This script works well on smaller data sets, but once I start trying to use this on sets of data ranging from 100-500K it goes through the process of filtering, but nothing remains afterwards. I'm confused as to why it works on smaller data but not larger data sets. Any ideas on what I can do?