How to lock cells from moving when other cells are deleted/added in Excel?

Question

I have been scouring the internet for an answer and simply cannot find one. I have a large list with numbers in each row, at the bottom of the list it totals the numbers in the particular row. I want to be able to delete rows from this list above, but not worry about the total line shifting up since deleting a cell in Excel makes the row "shift up, shift left, etc etc". End goal: I want to be able to delete cells/rows, but not shift the total line up. Freezing frames, or splitting the window is not the answer. I simply want to lock a selection of cells in place so no matter what I do, they do not move. Can anyone help?

Answer 1

Just delete the content, not the row. If I delete Row 1, there still has to be a first row, so everything moves up including the row that contains your totals. You can not have vacuum space, a cell can be empty but there must be a cell.

Perhaps if you explain what you need to do and why you do not want your total row to move, we can be more helpful.

Answer 2

Here is one method to do as you wish. The following example assumes that you wish to sum the cells A1:A9 and return the total in cell A10.



Copy the following event handler to the clipboard (highlight the entire event handler, right click inside the highlighted area, and 'Copy'):



Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then

Application.EnableEvents = False

For i = 1 To 10

If Cells(i, "A").HasFormula Then

Cells(i, "A").Value = ""

End If

Next

Range("A10").Formula = "=Sum(A1:A9)"

Application.EnableEvents = True

End If

End Sub



Select any worksheet and right click the sheet tab at the bottom.



Select 'View Code'.



Paste the event handler into the white editing area to the right (right click inside the area and 'Paste').



Close the VBE (red button w/white 'x' - top right).



Make numeric entries in cells A1:A9 and the sum will be returned in A10.



Delete any row 1-9.



The total will still be returned in cell A10, however there will now be a blank cell in A9. Delete any other row and the total will still be returned in cell A10, but A8 and A9 will be blank. Enter values in A8 and/or A9 and the total in A10 will be adjusted.



Note: since you wish to have a 'fixed' range of rows, you cannot insert a row as that would modify your original 'fixed' range of rows.

Answer 3

I would suggest putting your column totals on the top of your list. This will stop your totals from moving around when deleting or adding rows below. I would add extra lines in the sum function (=SUM(B2:B1000 even though you only have 900 items in you list) this will catch any line you add later.

What you want to do can be done with VB code but I don't think you would like the results. If you have 1000 items in you list and you delete 100 items, you will have to scroll way down on your worksheet to see your totals. If you print it you will have blank pages between your list and your totals.

Hope this helps.

John