Stop all cells from calculating after deleting a row

[J. Caplan]

I have formula that is used in many of the cells of a worksheet. The formula
hits a database behind the scenes and can be time consuming when run one at a
time. I provide a tool bar button that will gather data from the cells and
do a single DB hit, thus speeding up the process a great deal.

One thing I have noticed, though, is when I delete a row in the worksheet,
Excel goes through and recalculates all cells. I know I can set Calculation
mode to manual before deleting a row, but is there a way to stop this from
happening without setting calculation to Manual?
Is there a way to capture the fact that this is going to happen so that I
can call my routine to cache data first?

 

[sbitaxi]

I have formula that is used in many of the cells of a worksheet.  The formula
hits a database behind the scenes and can be time consuming when run one at a
time.  I provide a tool bar button that will gather data from the cellsand
do a single DB hit, thus speeding up the process a great deal.

One thing I have noticed, though, is when I delete a row in the worksheet,
Excel goes through and recalculates all cells.  I know I can set Calculation
mode to manual before deleting a row, but is there a way to stop this from
happening without setting calculation to Manual?
Is there a way to capture the fact that this is going to happen so that I
can call my routine to cache data first?

You could use a simple subroutine to delete the row of the cell you
have selected which turns calculation off and on. Use the following
code. Under Macro options, set it to a key combination so you can Ctrl+
(YOUR KEY) when you want to delete a row. This will work on multiple
selections as well.

Good luck!


Steven

Sub RemRowNoCalc ()
'Disable calculations and screen updating
With Application
.DisplayAlerts = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
.StatusBar = False
End With
'Delete your rows
With Selection
..EntireRow.Delete
End With

're-activate screen updating and auto-calc
With Application
.DisplayAlerts = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.StatusBar = False
End With
End Sub