Shortcut to enter new table row with formula

[HNarli]

Hi All,

I have a table with a lot of formula and when I insert a new row it is always blank and doesn't carry the formula downward into the new row. I did some digging on Google and managed to find a work-around someone else had proposed which enters a new row (with all the formula) each time you double click within the table. This works very well and I will post the macro below. However... I have a need to double click into most rows when we enter data so this method isn't practical for us because every time I double click I get new rows, even if I don't necessarily need them! I really like the speed in which the double click works though so I wondered if anyone would be kind enough to tell me if we could have the same macro but double-click of the RIGHT cursor? This was general double clicking can be kept as usual but right double clicking would enter a new row? If not then any other suggestions welcome!

Thanks, here is the macro I was using:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Updateby Extendoffice 20160725
Cancel = True
Target.Offset(1).EntireRow.Insert
Target.EntireRow.Copy Target.Offset(1).EntireRow
On Error Resume Next
Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
End Sub

 

[cooper645]

Not a double right click but a single one

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Offset(1).EntireRow.Insert
Target.EntireRow.Copy Target.Offset(1).EntireRow
On Error Resume Next
Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
End Sub

 

[HNarli]

Hi, thanks for your quick reply but this only works when I right click, not double right-click. I tried adapting your code to say BeforeDoubleRightClick, but it doesn't like it. Do you have any other ideas please? Or another suggestion? I'm happy with a keyboard shortcut lik ctrl N for 'New row' or something? Thanks

 

[cooper645]

If you start the macro recorder and assign the keyboard shortcut you require, then click a couple of cells and stop the macro recorder.
Then open the VBA editor and enter the code below into the macro after removing whatever in it, you will achieve your aim:

Sub NewRow()

' Keyboard Shortcut: Ctrl+Shift+N

ActiveCell.Offset(1).EntireRow.Insert
ActiveCell.EntireRow.Copy ActiveCell.Offset(1).EntireRow
On Error Resume Next
ActiveCell.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents

End Sub

An alternative is to copy the above macro into a module, then go back to Excel:

Alt + F8
Select macro
Then Options
Enter key combo of choice
Click OK

 

[Dave Patton]

With a Table go to end of last entry (D3), press Tab and a new row is created complete with formulas.

With a regular range such as A7:D8, move to cell D9
and press Ctrl+d. The formula above is copied down.

T202010b.xlsm
	A	B	C	D
1	Date	Deposit	Withdrawal	Balance
2	1-Oct-20	20,000.00		20,000.00
3	15-Oct-20		-5000	25,000.00
4	31-Oct-20			25,000.00
5
6	Date	Deposit	Withdrawal	Balance
7	1-Oct-20	20,000.00		20,000.00
8	15-Oct-20		-5000	25,000.00
9	31-Oct-20			25,000.00
1b

Cell Formulas
Range	Formula
D2:D4	D2	=SUM($B$2:B2)-SUM($C$2:C2)
D7:D9	D7	=SUM($B$7:B7)-SUM($C$7:C7)

 

[cooper645]

Ctrl Shift +

This will also insert a new row from wherever the active cell is

 

[HNarli]

Ctrl Shift +

This will also insert a new row from wherever the active cell is

Hi, Thanks for the suggestion but I've tried this and it's not quite right as it seems to put wrong formula into one of the cells which is odd! Add doesn't copy all of the styling.
Could I swap out the 'BeforeDoubleClick' for a unique, new keyboard shortcut? Any ideas welcome.
Thank you anyway!

 

[HNarli]

With a Table go to end of last entry (D3), press Tab and a new row is created complete with formulas.

With a regular range such as A7:D8, move to cell D9
and press Ctrl+d. The formula above is copied down.

T202010b.xlsm
	A	B	C	D
1	Date	Deposit	Withdrawal	Balance
2	1-Oct-20	20,000.00		20,000.00
3	15-Oct-20		-5000	25,000.00
4	31-Oct-20			25,000.00
5
6	Date	Deposit	Withdrawal	Balance
7	1-Oct-20	20,000.00		20,000.00
8	15-Oct-20		-5000	25,000.00
9	31-Oct-20			25,000.00
1b

Cell Formulas
Range	Formula
D2:D4	D2	=SUM($B$2:B2)-SUM($C$2:C2)
D7:D9	D7	=SUM($B$7:B7)-SUM($C$7:C7)

Hi,
Thanks for the suggestion but it doesn't work, it seems that when I try it carries some formula into the wrong column and doesn't copy all of the styling. The macro with the double click works perfectly, but as above I can't use this as we have the need to double click. Could I swap out the 'BeforeDoubleClick' for a unique, new keyboard shortcut? Any ideas welcome.

Thanks

 

[cooper645]

If you start the macro recorder and assign the keyboard shortcut you require, then click a couple of cells and stop the macro recorder.
Then open the VBA editor and enter the code below into the macro after removing whatever in it, you will achieve your aim:

Sub NewRow()

' Keyboard Shortcut: Ctrl+Shift+N

ActiveCell.Offset(1).EntireRow.Insert
ActiveCell.EntireRow.Copy ActiveCell.Offset(1).EntireRow
On Error Resume Next
ActiveCell.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents

End Sub

An alternative is to copy the above macro into a module, then go back to Excel:

Alt + F8
Select macro
Then Options
Enter key combo of choice
Click OK

this can be assigned to a keyboard shortcut and will do what you require