Screen output while ScreenUpdate = False

[krissz]

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]

Does your macro have a lengthy loop in it? You can easily use the variable in this to show some indication of progress.

 

[VoG]

This may help (not an ideal link, I know).

 

[shg]

You can use the status bar, e.g.,

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

 

[MrKowz]

This may help (not an ideal link, I know).

Not a link at all

 

[VoG]

Not a link at all

Loosing it: http://www.mrexcel.com/forum/showthread.php?t=512719

 

[krissz]

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:

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]

You could do it like this ...

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]

Many thanks to everybody.
I went for the idea proposed in

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.