Screen output while ScreenUpdate = False

krissz

Board Regular
Joined
Feb 21, 2010
Messages
92
I have a rather long macro which copies, pastes & sorts across some 60 or more sheets (the actual number can vary). With the screen visible, I finish up with a jumbled mess for a few minutes - sometimes much longer. With ScreenUpdate set to False, the user has no indication of progress and there is a risk that the task could be aborted or confidence lost in the process.

What I have been trying to generate is a message box or similar which can provide an indication of progress. There is a sequence of specific tasks etc which, if displayed, can provide an indicator of progress.

Any help or suggestion would be appreciated.
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,649
Does your macro have a lengthy loop in it? You can easily use the variable in this to show some indication of progress.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,714
Office Version
2010
Platform
Windows
You can use the status bar, e.g.,

Code:
Application.StatusBar = "Step 1 of 17..."
 

krissz

Board Regular
Joined
Feb 21, 2010
Messages
92
Thank you everybody for the help. I followed the link from MrKowz & VoG. Some playing around and I have full progress information for the user.
This is particularly important as the routine is now taking some 10 minutes to run (& could take longer as data fills up). The bulk of the time is in a process that deletes unused columns - the first process selects valid rows and copies to another sheet. I would appreciate any suggestions for speeding this up. The code is as follows:

Code:
Set L_Range = Worksheets("Needed").Range("P1:IV1")
    Rows_Used = WorksheetFunction.CountA(L_Range)
    C_Rows = 0
    
    Sheets("Needed").Select
    Range("P1").Select
    Do While ActiveCell <> ""
        If ActiveCell.Offset(1, 0) = 0 Then
             ActiveCell.EntireColumn.Delete
            
        Else
            '   Build quantity - move to next row
            ActiveCell.Offset(0, 1).Activate
            To_Manufacture = To_Manufacture + 1
        
        End If

        C_Rows = C_Rows + 1
        PctDone = C_Rows / Rows_Used
        With Progress_Bar
            .FrameProgress.Caption = Format(PctDone, "0%") & " of " & Rows_Used & " Columns Processed"
            .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
        End With
        DoEvents
    
    Loop
My only thought was possibly to sort the columns depending on the content of row = 2, then delete those with a zero value in one fell swoop & resort back to the original sequence. Would this help ?

Many Thanks
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,714
Office Version
2010
Platform
Windows
You could do it like this ...
Code:
Sub x()
    Dim cell        As Range
    Dim rDel        As Range
    Dim iToDo       As Long
 
    With Worksheets("Needed")
        For Each cell In .Intersect(.UsedRange, .Range("P1:IV1"))
            If cell.Offset(1).Value = 0 Then
                If rDel Is Nothing Then Set rDel = cell.EntireColumn
                Set rDel = Union(rDel, cell.EntireColumn)
            Else
                iToDo = iToDo + 1
            End If
        Next cell
    End With
 
    If Not rDel Is Nothing Then
        Application.StatusBar = "Deleting columns ..."
        rDel.Delete
        Application.StatusBar = False
    End If
End Sub
 

krissz

Board Regular
Joined
Feb 21, 2010
Messages
92
Many thanks to everybody.
I went for the idea proposed in
HTML:
http://spreadsheetpage.com/index.php/site/tip/displaying_a_progress_indicator/
.
After some mods, it works very well.
Also thanks to some ther ideas, my routine has reduced from 9 minutes to 25 seconds.
 

Forum statistics

Threads
1,082,109
Messages
5,363,193
Members
400,721
Latest member
eileen123

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top