macro monitor

**chloe88** · 08-30-2011, 06:00 AM

Hi all, I am running a macro over a large spreadsheet (~100000 lines). Obviously this will take sometime. Is there a way of knowing the progress of the macro? Thanks in advance, Chloe

The icon is saying "Application not responding" but when I use the 'top' command excel seems to be working - can I assume it is just taking some time?

**Domski** · 08-30-2011, 06:08 AM

You can update the status bar like this:

Sub test()

Dim lngMyCount As Long
Dim lngCountTo As Long

lngCountTo = 10

For lngMyCount = 1 To lngCountTo
    ' Pause just for demo purpose
    Application.Wait (Now + TimeValue("0:00:01"))
    ' Update status bar with record count
    Application.StatusBar = "Processing Record " & lngMyCount & " of " & lngCountTo
Next lngMyCount

' Return control of status bar to Excel
Application.StatusBar = False

End Sub

Dom

**chloe88** · 08-30-2011, 06:13 AM

Originally Posted by Domski

You can update the status bar like this:

Sub test()

Dim lngMyCount As Long
Dim lngCountTo As Long

lngCountTo = 10

For lngMyCount = 1 To lngCountTo
    ' Pause just for demo purpose
    Application.Wait (Now + TimeValue("0:00:01"))
    ' Update status bar with record count
    Application.StatusBar = "Processing Record " & lngMyCount & " of " & lngCountTo
Next lngMyCount

' Return control of status bar to Excel
Application.StatusBar = False

End Sub

Dom

Thank you. Should I add it as a new macro or to the beginning of the existing macro?

**Domski** · 08-30-2011, 06:20 AM

That's really just an example. Assuming you have a loop of some kind you would need to use similar to this within the loop to update the status bar:

Application.StatusBar = "Processing Record " & lngMyCount & " of " & lngCountTo

Don't forget to return control to Excel once the loop has finished and in any error handling within your code.

Dom

**snb** · 08-30-2011, 06:34 AM

If you have a slick design of the worksheet and well -wrought VBA code you won't need a monitor.
Post your code so we can help you to speed it up.

**chloe88** · 09-07-2011, 11:24 AM

Here is my code,

Sub DeleteRecords()
Dim xlCalc As XlCalculation
Dim rRange As Range

Sheets("Sheet1").Activate
'Store current Calculation then switch to manual.
'Turn off events and screen updating
With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With

'Remove any filters
ActiveSheet.AutoFilterMode = False
With Range("J1:J" & ActiveSheet.UsedRange.Rows.Count)
.AutoFilter Field:=1, Criteria1:="Y"
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

'Remove any filters
ActiveSheet.AutoFilterMode = False

'Revert back
With Application
.Calculation = xlCalc
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub