Insert Row with formula on double click & delete last row within a defined Range

[vvkprabhu]

Can someone help me with a macro for the below:

Whenever i double click between a defined range say row 1 to 100, row 105 to 204 and so on.... excel should insert a row and copy the formula automatically from the top. At the same time it should delete the last row within the defined range.

For example: If i double click on B5, excel should insert a row between row 5 & 6 and copy the formula from the top automatically. At the same time it should delete the last row 100.

[Jan Karel Pieterse]

If you convert your range to a table, formula insertion is done automatically. Then this should suffice:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Target.ListObject.ListRows.Add Target.Row - 1
Target.ListObject.ListRows(Target.ListObject.ListRows.Count).Delete
Cancel = True
End Sub