Excel VBA Progress Bar.

[kashif.special2005]

Hi

I am using this code for deleting unnecessary rows in a sheet. And it is taking 5 minutes to delete the unnecessary rows, I want that when rows is deleting, a form show that rows is deleting please wait with progressing completing percentage.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
<o>Below code is for deleting rows.</o>
<o> </o>
For i = last_row To 2 Step -1<o></o>
<o> </o>
If Cells(i, 3).Value = "Combined" Or Cells(i, 3).Value = "Medium" Or _<o></o>
Cells(i, 1).Value = "Clnt Id" Or Cells(i, 1).Value = "Total Medium" Then<o></o>
<o> </o>
Rows(i).Delete<o></o>
i = i + 1<o></o>
End If<o></o>
Next i
<o> </o>
Thanks & Regards,
Kashif.<o></o>

 

[Jonmo1]

Adding a progress indicator will only make it slower....

Try this to make it much much faster..

Dim PrevCalc As Variant, i As Long
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    PrevCalc = .Calculation
    .Calculation = xlCalculationManual
End With
 
For i = last_row To 2 Step -1<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
If Cells(i, 3).Value = "Combined" Or Cells(i, 3).Value = "Medium" Or _<o:p></o:p>
Cells(i, 1).Value = "Clnt Id" Or Cells(i, 1).Value = "Total Medium" Then<o:p></o:p>
<o:p></o:p>
Rows(i).Delete<o:p></o:p>
'i = i + 1 '<---This is not necessary.
End If<o:p></o:p>
Next i
 
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = PrevCalc
End With

Hope that helps.

 

[VoG]

Let us try to speed it up

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For i = last_row To 2 Step -1
    If Cells(i, 3).Value = "Combined" Or Cells(i, 3).Value = "Medium" Or _
    Cells(i, 1).Value = "Clnt Id" Or Cells(i, 1).Value = "Total Medium" Then
        Rows(i).Delete
    End If
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

 

[SuperFerret]

Hi Kashif,

If you do a search on the forum there are a few threads on this that may help you.

This post from Datsmart has a few links:
http://www.mrexcel.com/forum/showpost.php?p=1504844&postcount=2

Good thread title by the way!

 

[Trevor G]

Try something like this

Step 1

To create an Excel VBA progressbar...

1. Open a new Workbook.

2. Press ALT + F11 to open the VBA Editor.

3. Create a UserForm - right click on anything in the VBProject pane at the top left and click on Insert > UserForm.

4. Change the caption of the UserForm to <i>Please wait...</i>.

5. Create a label covering the whole form...

Step 2

Double click on the label, delete the code, and add this code...
Private Sub UserForm_Activate()
' Set the width of the progress bar to 0.
frmProgressBar.LabelProgress.Width = 0
Call Main
End Sub
7. Create a new Module - right click on anything in the VBProject pane at the top left and click on Insert > Module.

8. Add this code...

Sub Excel_VBA_ProgressBar()
frmProgressBar.Show
End Sub

Sub Main()
Dim pctCnt As Integer
Dim cnt As Integer
Dim Completed As Single
Application.ScreenUpdating = False
pctCnt = 1
For cnt = 1 To 100
For a = 1 To 500000
Next
pctCnt = pctCnt + 1
Completed = pctCnt / 100
frmProgressBar.LabelProgress.Width = Completed * frmProgressBar.Width
DoEvents
Next
Unload frmProgressBar
End Sub

9. Place the cursor in any one of the three lines of the VBA Progressbar Sub to run it.

 

[kashif.special2005]

Hi All,

Thanks for reply, But I want it in different way, like example file in below link,

http://spreadsheetpage.com/index.php/file/progress_indicator_demo/

I want that from my module when this will be running then that progress bar show with percentage completion and when this loops end unload that progress bar form.


For i = last_row To 2 Step -1
'start progress bar form from here...

If Cells(i, 3).Value = "Combined" Or Cells(i, 3).Value = "Medium" Or _
Cells(i, 1).Value = "Clnt Id" Or Cells(i, 1).Value = "Total Medium" Then

Rows(i).Delete
i = i + 1
End If
Next i
'Unload progress bar form from here

Thanks & Regards,
Kashif.

 

[VoG]

Why do you have

i=i+1

 

[kashif.special2005]

Hi VoG,

I forget to remove that line from code...........

Thanks,
Kashif.

 

[pmich]

'copy this in UserForm1 which has a Frame containing a label
UserForm1.FrameProgress.Visible = True
For i = last_row To 2 Step -1
'start progress bar form from here...
'''''''''''''''PROGRESS BAR PART-A BEGINS'''''''''''''''''''
Dim pctCent As Long
Dim PrcntgCompleted As Single
Dim TotRunNeeded As Long
TotRunNeeded = 1000 'Total no of rows
Application.ScreenUpdating = False
pctCent = 0
FrameProgress.Visible = True
'''''''''''''PROGRESS BAR PART-A ENDS'''''''''''''''''
If Cells(i, 3).Value = "Combined" Or Cells(i, 3).Value = "Medium" Or _
Cells(i, 1).Value = "Clnt Id" Or Cells(i, 1).Value = "Total Medium" Then
Rows(i).Delete
'''''''''''''''PROGRESS BAR PART-B BEGINS'''''''''''''''''''
For a = 1 To 5000000 'to delay the progress bar
Next
pctCent = pctCent + 1
PrcntgCompleted = pctCent / TotRunNeeded ' Update the percentage completed.
' Update the Caption property of the Frame control.
UserForm1.FrameProgress.Caption = "Checking rows " & _
Format(PrcntgCompleted, "0%") & " Completed! Wait!"
UserForm1.LabelProgress.Width = PrcntgCompleted * _
(UserForm1.FrameProgress.Width - 380) 'change 380 if nercessary
DoEvents
'''''''''''''PROGRESS BAR PART-B ENDS'''''''''''''''''
End If
Next i
'Unload progress bar form from here
'''''''''''''''PROGRESS BAR PART-C BEGINS'''''''''''''''''''
Application.ScreenUpdating = True
UserForm1.FrameProgress.Visible = False
'''''''''''''''PROGRESS BAR PART-C ENDS'''''''''''''''''''

 

[pmich]

Change the forecolor and backcolor properties of Frame and Label using RGB(205, 51, 51) or RGB(255, 228, 196) or RGB(0, 255, 255)