Total Row Not Calculating Correctly

[Jamie]

I don’t know if you need all this background to answer my question, but it
couldn’t hurt.

I am working in Excel 2002. Currently I have a table (A1:U14), 13 rows of
data, row 14 is the Total row. There are 2 columns (columns F and J) that
have formulas which calculate 3 columns preceding them (column F’s formula is
=SUM(C5:E5), column J’s formula is =SUM(G5:I5). These 2 columns with the
formulas are locked cells. The Total row has formulas which calculate the
figures in each column, the formula is =SUM(C5:C13), the cells in this row
are also locked.

I have unlocked all the cells that I want the user to have access to and
have protected the sheet.

Rows can be inserted into this table, by the user with this macro which is
assigned to a command button:

Sub AddRow()

Dim Msg, Style, Title, Response
Dim rngAdd As Range
Set rngAdd = ActiveCell

Msg = "Do you want to insert a row?" 'Define message.
Style = vbYesNo + vbDefaultButton2 'Define buttons.
Title = "Insert a Row" 'Define MsgBox title.

Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then 'User chose Yes.

ActiveSheet.Unprotect

rngAdd.Offset(1, 0).EntireRow.Insert
rngAdd.EntireRow.Copy

Set rngAdd = rngAdd.Offset(1, 0)

rngAdd.PasteSpecial xlPasteFormats
rngAdd.PasteSpecial xlPasteFormulas
rngAdd.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents

ActiveSheet.Protect

End If

End Sub

When figures are entered into the cells of this new row, the formulas in the
Total row do not recognize that a row has been added. When I unprotect the
sheet, the formula in the Total row is =SUM(C5:C13). The formula should
change to =SUM(C5:C14) with the addition of the new row.

I’m assuming it is not updating due to the cells being locked. How can I
have the Total row recognize a new row has been added, yet keep the user from
enter information into the cells of the Total row by mistake?

 

[George Nicholson]

If you want your column total formulas to update, you can't insert rows
below the last row of data (protection is immaterial).
The work around I use is to always insert rows above the last line, copy
the last line of data "up" into the new row & then clear the last
row for new data entry. Modifying a section of your code:

rngAdd.Offset(0, 0).EntireRow.Insert 'Add a row *above* the current
position
rngAdd.EntireRow.Copy 'Copy the row you just
"moved down"

rngAdd.Offset(-1, 0).EntireRow.PasteSpecial xlPasteAll 'Paste (Move)
the old data "up" into newly created row
rngAdd.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
'Clear old row so that it *appears* to be a "new" row

Of course, if your users are on the 1st row of your data range when they
use this you will have the same problem in reverse: your column totals won't
include the new row. If this is a possibility, you'd need to test for the
location of the active cell and then branch to either this or your existing
method (which will work for 1st row insertions).

The trick is to always insert *within* the range referred to by Grand total
formulas. As long as you do, those formulas will update themselves.

HTH,