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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

cooper645

Well-known Member
Joined
Nov 16, 2013
Messages
622
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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
 

HNarli

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

Well-known Member
Joined
Nov 16, 2013
Messages
622
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,569
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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)
 

cooper645

Well-known Member
Joined
Nov 16, 2013
Messages
622
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Ctrl Shift +

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

HNarli

New Member
Joined
Jun 26, 2018
Messages
24

ADVERTISEMENT

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

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

cooper645

Well-known Member
Joined
Nov 16, 2013
Messages
622
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,994
Messages
5,599,250
Members
414,299
Latest member
thenewworld

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
Top