Insert rows in table with protected/locked columns with formulae


New Member
Jul 17, 2019
Hi all!
I have a tablein Excel in which some columns need to be locked, and other columns need to beunlocked so people can enter data there. The columns that need to stay lockedhave formulae in each cell which use data that is entered in the unlockedcolumns.
Peopleusing the table should be able to insert rows whilst some of the columns remainlocked and the formulae are copied in the newly inserted rows.

I am unableto figure out how to allow for this. Therefore, my question is how can youinsert rows in a table where some of the columns are locked? It is necessarythat the formulae in the locked columns are copied when a new row is inserted.
Does this require a macro?

Any helpwould be much appreciated!

Some videos you may like

Excel Facts

Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off


Well-known Member
May 24, 2005
If your data is in a table, and the sheet is not protected, then formulas will be copied into the blank row if you right-click on the table and select Insert | Table Rows Above.

You could leave the columns unlocked and use a macro to refresh the formulas in the old and new rows based on a one more workbook/worksheet events (like workbook save or worksheet activate, or calculate). There are other events available that trigger more often, but you probably don't want to have the formulas refreshed each time the selection was changed.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...