I found this snippet of code, and it works beautifully for my need to be able to insert a row, maintaining formulas and formats, but clearing constants.
Private Sub Worksheet_BeforeDoubleClic
k(ByVal Target As Range, Cancel As Boolean)
'David McRitchie, 2007-09-07 insrtrow.htm on double-click
'-- will copy more often than Extend Formulas and Format (tools option)
Cancel = True
Cells(Target.Row + 1, 1).EntireRow.Insert
Cells(Target.Row + 1, 1).EntireRow.Select
Application.CutCopyMode = False
On Error Resume Next
'-- customize range for what cells constants can be removed --
On Error GoTo 0
I would like to make a couple of tweaks:
1. I would like to limit the macro to inserting rows below Row 20. That way, users would be unable to screw up any page formatting in Rows 1-19.
Even better, I'd like to limit the macro to operate on a table. That way, the macro will never be able to run on any rows BELOW the last line of the table.
Related topic: Is there a way to allow users to delete rows, but only within a range of rows? I know I can protect a sheet from row deletion, but it's a global allow/disallow thing.
And finally, can I limit the scrolling to the middle portion only? In this case it would be Ross-Master-Quote---Macro-enabled-Expert
Row 21-24 (plus any additional inserted rows above Row 24.)
I've attached a copy of the file, with personal info redacted.