Shortcut to enter new table row with formula

HNarli

New Member
Joined
Jun 26, 2018
Messages
24
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Not a double right click but a single one

VBA Code:
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
 
Upvote 0
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 :-)
 
Upvote 0
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:

VBA Code:
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
 
Upvote 0
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
ABCD
1DateDepositWithdrawalBalance
21-Oct-2020,000.0020,000.00
315-Oct-20-500025,000.00
431-Oct-2025,000.00
5
6DateDepositWithdrawalBalance
71-Oct-2020,000.0020,000.00
815-Oct-20-500025,000.00
931-Oct-2025,000.00
1b
Cell Formulas
RangeFormula
D2:D4D2=SUM($B$2:B2)-SUM($C$2:C2)
D7:D9D7=SUM($B$7:B7)-SUM($C$7:C7)
 
Upvote 0
Ctrl Shift +

This will also insert a new row from wherever the active cell is
 
Upvote 0
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!
 
Upvote 0
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
ABCD
1DateDepositWithdrawalBalance
21-Oct-2020,000.0020,000.00
315-Oct-20-500025,000.00
431-Oct-2025,000.00
5
6DateDepositWithdrawalBalance
71-Oct-2020,000.0020,000.00
815-Oct-20-500025,000.00
931-Oct-2025,000.00
1b
Cell Formulas
RangeFormula
D2:D4D2=SUM($B$2:B2)-SUM($C$2:C2)
D7:D9D7=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
 
Upvote 0
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:

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top