Irregular Macro Performance when Deleting DBRW formulas

[lostit]

I have a macro that is cycling through a collection that then utilizes selected members of that collection to flag selected excel rows for deletion. The coding seems to be relatively simple but the issue is this:

A.) There are approximately 2000 lines that it has to delete.
B.) On 2 of the office computers it takes approximately 20-30 seconds.
C.) On 2 other office computers, it takes approximately 90 minutes

***The behavior is completely replicatable, and happens 100% of the time. The fast PC's are always fast, and the slow PC's are always slow.

The code is below, but the following steps have already been taken:

1.) Screen updating is turned off
2.) Calculation is turned off
3.) All other files are closed when this file runs

The PC's are almost all identical (Dell Laptops, same models, approx 1 year old, 2 gig of RAM), utilizing Windows XP and Excel 2003 SP4(?). There is no difference in addins or reference libraries between the PC's, and no significant difference in installed software. There are also no noticeable drains in the task manager or processes hitting the CPU, besides Excel. The CPU is topping out at approx 55% on the slow running PC's. Not sure on the fast running ones.

Here is the code:
________________________________________________________________

Sub RemoveDetailRows()

Dim Cell As Range
Dim DetailRows As New Collection
Dim TotalRows As New Collection

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each Cell In g_rng_CurRpt_DetailRows '**** This is the collection that defines the range to be scanned for the "1" flag
If Cell.Value = 1 Then DetailRows.Add Cell
Next

For Each Cell In DetailRows
Cell.EntireRow.Delete '****** THIS IS THE PROBLEM LINE *******
Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

_________________________________________________________________

Basically on the fast preforming PC's the problem line is instantaneous, on the slow performing PC's the problem line takes approx 5 seconds to execute. Multiply that 5 second execution time by 2000 executions and I've got a problem.

The only other piece of information is that I was able to speed execution up significantly, by hardcoding the formulas prior to line deletion. Theoretically this should make no difference as the calculation was turned off, but it did. Still not as fast as the 2 faster performing PC's, but performance went from 90 minutes to 3-4 minutes. Its almost as if its still calcing on the 2 slow ones and thats whats holding me up. I say almost, because the formulas in the cells are mostly DBRW formulas linking to Cognos TM1, and the data is not changing, so it appears that I'm not actually getting any new data, or calculations in those cells, while the process is running.

IMO, its almost as if there is a setting somewhere that I'm missing. If anyone has any idea what might be causing the slow execution of the exact same code, in these 2 PC's, I would greatly appeciate it.

[LoadzaGrunt]

Before running the delete loop, I would be tempted to add:

Code: Select all

blnAppEvents = Application.EnableEvents
Application.EnableEvents = False

And afterward:

Code: Select all

Application.EnableEvents = blnAppEvents

Does that help ?

[TJMurphy]

No idea why one PC is faster than the other but another optimisation to try is to do a union of all the cells to delete and then delete them all in one go ...

This is my code for hiding rows, should be able to do something similar with delete (I've put in the command in a comment at the end).

Code: Select all

Sub HideZeroRows()
   Dim rCell As Range
   Dim rRows As Range

   If Selection.Columns.Count <> 1 Then End
   For Each rCell In Selection.Cells
      If rCell.Value = 0 Then
         If rRows Is Nothing Then
            Set rRows = rCell
         Else
            Set rRows = Union(rRows, rCell)
         End If
      End If
   Next rCell
   rRows.EntireRow.Hidden = True  ' rRows.EntireRow.Delete 
End Sub

[Wim Gielis]

Hi

Why not coding the Autofilter or the Advanced filter? (If I understand the problem well).

In my experience, with large spreadsheets, this is often a multitude faster than loops.

Indeed, first turn off automatic calculation, events, screenupdating and the statusbar if applicable.

Wim

[lostit]

Before running the delete loop, I would be tempted to add:

Code: Select all

blnAppEvents = Application.EnableEvents
Application.EnableEvents = False

And afterward:

Code: Select all

Application.EnableEvents = blnAppEvents

Does that help ?

I already tried the enableevents trick, but it had no impact. However, what is the "blnAppEvents" meant to accomplish? I haven't seen that before.

Thanks for the reponse.

[lostit]

No idea why one PC is faster than the other but another optimisation to try is to do a union of all the cells to delete and then delete them all in one go ...

This is my code for hiding rows, should be able to do something similar with delete (I've put in the command in a comment at the end).

Code: Select all

Sub HideZeroRows()
   Dim rCell As Range
   Dim rRows As Range

   If Selection.Columns.Count <> 1 Then End
   For Each rCell In Selection.Cells
      If rCell.Value = 0 Then
         If rRows Is Nothing Then
            Set rRows = rCell
         Else
            Set rRows = Union(rRows, rCell)
         End If
      End If
   Next rCell
   rRows.EntireRow.Hidden = True  ' rRows.EntireRow.Delete 
End Sub

I give this a shot. I was thinking that selecting/grouping all the flagged rows and deleting at once might solve the issue but couldn't figure out how to approach it. I'll let you know if it works, and thanks for the response.

[lostit]

Hi

Why not coding the Autofilter or the Advanced filter? (If I understand the problem well).

In my experience, with large spreadsheets, this is often a multitude faster than loops.

Indeed, first turn off automatic calculation, events, screenupdating and the statusbar if applicable.

Wim

I understand how to get to the filter part, as its easy to filter by the "flag" column that its utilizing, but how would you go about deleting those rows that it selected, without then having to cycle through them in a loop and delete individually? On the other hand, if I filtered and then deleted the lines it selected in one shot, wouldn't I also delete the unselected line, which the filter merely hides?

Thanks again for your time and help on this.

[Wim Gielis]

I understand how to get to the filter part, as its easy to filter by the "flag" column that its utilizing, but how would you go about deleting those rows that it selected, without then having to cycle through them in a loop and delete individually? On the other hand, if I filtered and then deleted the lines it selected in one shot, wouldn't I also delete the unselected line, which the filter merely hides?

Hi

I understand your worries, but you shouldn't worry.

In fact, when you autofilter manually, you can select a range with the mouse e.g., then press F5, then Special..., then "Visible cells". This gives you a range with... well... visible cells Then, manually you can delete the entirerow. This range will not be contiguous in many cases, but it does not contain the rows that did not match the Autofilter criteria.

With VBA-code, you use:

Code: Select all

SpecialCells(xlCellTypeVisible)

to return the visible cells. EntireRow then gives the complete row, after which you write the Delete.

For similar sample code, check out here with Debra Dalgleish:

http://www.contextures.com/xlautofilter03.html

Go to Copy Filtered Rows

The code seems a bit long-winded but there are checks whether the autofilter returns results or not. If not, SpecialCells(xlCellTypeVisible) would raise an error.

The whole point is to avoid the loops.

Coding the advanced filter is analogous but more powerful given the criteria range.

HTH,

Wim

[lostit]

No idea why one PC is faster than the other but another optimisation to try is to do a union of all the cells to delete and then delete them all in one go ...

This is my code for hiding rows, should be able to do something similar with delete (I've put in the command in a comment at the end).

Code: Select all

Sub HideZeroRows()
   Dim rCell As Range
   Dim rRows As Range

   If Selection.Columns.Count <> 1 Then End
   For Each rCell In Selection.Cells
      If rCell.Value = 0 Then
         If rRows Is Nothing Then
            Set rRows = rCell
         Else
            Set rRows = Union(rRows, rCell)
         End If
      End If
   Next rCell
   rRows.EntireRow.Hidden = True  ' rRows.EntireRow.Delete 
End Sub

This actually worked for me, but I had to make a modification. If I ran your code as is, I got the same issue as I had, in that the delete line itself is what hung. In the case of this code, it hung for almost 30 minutes. Its almost like I simply took my 5 second lag, per execution, and combine(& added them together) to get one big long lag when I tried to execute the delete one time.

What I did was combine a clearcontents command into the code, to slowly reduce the total amount of data that it was dealing with. So I built up my "rrow" object with all of the combined addresses, but then cleared the contents of the flagged line at the same time. Once I had finished cycling through all of my detail, and clearing contents, I deleted the flagged, but empty rows, and got the performance I was looking for on all machines.

My performance was as follows:
Cycled through 10,000+ lines of detail (up from the 2,000+ in original example) and deleted down to about 200 remaining lines (the original base report). The original 2,000 line deletion under the old code took approximately 90 minutes. The new code with 10,000 lines takes exactly 19 seconds.

Here is the code so that you can see the changes. Thanks again to everyone for their feedback.
______________________________________________________________________________________

Sub RemoveDetailRows()

Dim Cell As Range
Dim DetailRows As New Collection
Dim detailRows_2 As New Collection
Dim TotalRows As New Collection
Dim DeletionRows As Range

wksReport.Unprotect g_Password
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each Cell In g_rng_CurRpt_DetailRows
If Cell.Value = 1 Then DetailRows.Add Cell
Next

For Each Cell In DetailRows
If Cell.Value = 1 Then
If DeletionRows Is Nothing Then
Set DeletionRows = Cell
Cell.EntireRow.ClearContents
Else
Set DeletionRows = Union(DeletionRows, Cell)
Cell.EntireRow.ClearContents
End If
End If
Next Cell

If Not (DeletionRows Is Nothing) Then DeletionRows.EntireRow.Delete

Application.ScreenUpdating = True
wksReport.Protect g_Password

End Sub
______________________________________________________________________________________

[Wim Gielis]

Nevertheless, I would be interested to see if the filters would perform better; if you'd be interested too, leave a small sample file so that I can write the code and then you can test it on your 10,000 lines.

Wim

[lostit]

Nevertheless, I would be interested to see if the filters would perform better; if you'd be interested too, leave a small sample file so that I can write the code and then you can test it on your 10,000 lines.

Wim

I tried it and found the performance to actually be a little slower, approx 32 seconds. It was also more complex for me to code and to track that I was only filtering/deleting certain lines. Regardless, the key part seems to be clearing the lines before attempting any form of deletion. If I filtered then deleted, without clearing, I still got the same performance issue. its almost as if I was hitting the TM1 database in some way during each deletion, but not returning any new values or recalcing.

As far as supplying a model, its not that easy as its a large model and ties into our TM1 cubes, so I would bet the problem is a combination of several things.

Either way, this ended up being a work around and never answered the core issue regarding similar machines behaving differently in this situation with identical code.