跳到主要內容

 在Excel中插入行時如何自動填充公式？

作者：小陽 最後修改時間：2017-05-02

在現有數據行之間插入空白行時，該公式不會復制並粘貼到新行中，您應該手動拖動或填充它。 但是，有時候，當您自動在數據范圍之間插入空白行時，您想填寫公式，如下圖所示，本文中，我將討論一些解決該任務的快速技巧。

doc自動填充公式插入第1行

創建表格插入空白行時自動填充公式

使用VBA代碼插入空白行時自動填充公式

創建表格插入空白行時自動填充公式

在Excel中，表格格式可以幫助您將上述公式自動填充到新插入的空白行中，請執行以下操作：

1. 選擇您要自動填充公式的數據范圍，然後單擊 插入 > 枱燈，請參見屏幕截圖：

doc自動填充公式插入第2行

2。 在 創建表格 對話框中，檢查 我的桌子有標題 如果您的數據中包含標題，請參見屏幕截圖：

doc自動填充公式插入第3行

3。 然後點擊 OK 按鈕，表格已創建，現在，當您插入空白行時，上述公式將自動插入到新行中，請參見屏幕截圖：

doc自動填充公式插入第4行

使用VBA代碼插入空白行時自動填充公式

如果您不希望將數據更改為表，則下面的VBA代碼也可以幫助您解決此問題。

1。 選擇包含您要自動填充的公式的工作表選項卡，然後右鍵單擊以選擇 查看代碼 從上下文菜單轉到 Microsoft Visual Basic for Applications 窗口，然後將以下代碼複製並粘貼到模塊中：

VBA代碼：插入空白行時自動填充公式

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

doc自動填充公式插入第5行

2。 然後保存並關閉此代碼，返回工作表，現在，當您雙擊數據之間的單元格時，將在活動單元格下面插入新行，並且也會自動填充公式。

演示：插入空白行時自動填充公式

最佳辦公生產力工具

🤖 Kutools 人工智慧助手：基於以下內容徹底改變數據分析： 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup  |   多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧   |  工作簿和工作表管理器   |  資源庫 （自動文字）   |  日期選擇器   |  合併工作表   |  加密/解密單元格   |  按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 （過濾粗體/斜體/刪除線...）...
前 15 個工具集:  12 文本 工具 (添加文本, 刪除字符，...）   |   50+ 圖表 類型 (甘特圖，...）   |   40+ 實用 公式 (根據生日計算年齡，...）   |   19 插入 工具 (插入二維碼, 從路徑插入圖片，...）   |   12 轉化 工具 (數字到單詞, 貨幣兌換，...）   |   7 合併與拆分 工具 (高級合併行, 分裂細胞，...）   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能，體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

產品描述

Office選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
Comments (14)
No ratings yet. Be the first to rate!
KelleyL
KelleyL
This comment was minimized by the moderator on the site
This works GREAT, until I protect the worksheet (to protect my forumulas in other cells). Can you let me know what i need to do to keep this functionality once the worksheet is protected?
Reply
0
0
Marcelo Moreira
Marcelo Moreira
This comment was minimized by the moderator on the site
Excellent simple piece of code!!!!
Reply
0
0
Niket Shet
Niket Shet
This comment was minimized by the moderator on the site
Hi,

This code is very helpful, however, I have a slightly different requirement. The formulas should be copied when the row is inserted manually, i.e. by "insert row".
Can you please resend the code after incorporating this change i.e. instead of "Worksheet_BeforeDoubleClick", some other trigger point to be used "insert row/etc"

Note - I do have any exposure to coding being from the Accounts background.


Regards
Niket Shet
Reply
0
0
Sanat Biswal
Sanat Biswal     Niket Shet
This comment was minimized by the moderator on the site
Hi Niket. You can see all the options for different trigger points in the tab itself.

For this click on CTRL + F11 to open the code editor as explained in the video above, and there will be 2 dropdowns on the top of the code which you would copy, click on the second dropdown and you can find many options for different trigger points.

The first dropdown would be ideally set to "Worksheet" and the second one would be set to "BeforeDoubleClick". You can change the second trigger point to your liking and use accordingly.

Hope it helps!
Reply
0
0
Jeff
Jeff     Sanat Biswal
This comment was minimized by the moderator on the site
I had the same question as Niket, so I tried looking at the list of trigger points in the second dropdown and I don't see anything that clearly works as Niket or I intend with the (select row+right click+insert row functionality). I tried the "BeforeRightClick" option but it just inserts a row w/formulas anytime I right click a cell, which is definitely not what I was looking for. Is there a resource somewhere that explains what each of the triggers can be used for? Or is there possibly another way to get to this functionality?

Thanks!
Jeff
Reply
0
0
David Banks
David Banks
This comment was minimized by the moderator on the site
I am using the code to insert a line on double-click. However, there is something wrong, Excel does not increment the first reference in my formula in the next but one row. However, it is corrected for all remaining rows.
I inserted a line at row 144 and this is what I got: (note S144 in 3rd line should be S145)
row 144 =(S143+D144+E144)-(K144+L144+M144+N144+O144+Q144+R144)
row 145 =(S144+D145+E145)-(K145+L145+M145+N145+O145+Q145+R145)
row 146 =(S144+D146+E146)-(K146+L146+M146+N146+O146+Q146+R146)
row 147 =(S146+D147+E147)-(K147+L147+M147+N147+O147+Q147+R147)
Any clues as to why it does this, I don't think it is the VBA???
David
Reply
0
0
gt
gt
This comment was minimized by the moderator on the site
Does the table method work in excel for xp (2002) and if yes, how?
Reply
0
0
RR
RR
This comment was minimized by the moderator on the site
The code works very well, but does not update cell reference in first row after the inserted row.See below" e.g 50 10 40 40 10 50 40 20 20
Reply
0
0
RR
RR
This comment was minimized by the moderator on the site
This code is very good, but it does not update the cell reference in the first following row after the inserted row. Can you please help? e.g: 50 10 40 40 10 50 40 20 20
Reply
0
0
Bilal shoukat
Bilal shoukat
This comment was minimized by the moderator on the site
Above formula is very helpful for me please tell me how to use this code permanently
Reply
0
0
Bilal shoukat
Bilal shoukat
This comment was minimized by the moderator on the site
dear above formula which helps to update the above formula on inserting row by double clicking. I want to know how to use this code permanently because when i close the sheet and reopen code finishes. Please help to solve the issue. This formula is very helpful for me
Reply
0
0
Sanat Biswal
Sanat Biswal     Bilal shoukat
This comment was minimized by the moderator on the site
Hi Bilal. You need to save the file as a "Macro Enabled" file to get the code to work once you close and reopen the file. To save the file as Macro, just use "Save as" and then select "Excel-Macro Enabled Workbook" to get the desired result.
Reply
0
0
David Hayes
David Hayes     Bilal shoukat
This comment was minimized by the moderator on the site
Very easy solution. Thank you very much.
Reply
0
0
Bhavin
Bhavin
This comment was minimized by the moderator on the site
I followed the VBA code mentioned above for my xls. I have one .xls with multiple sheets in it , i want if i insert a row in my one sheet at the start or any of the sheet for that matter it should auto insert row in all my sheet in the same worksheet. Also each of my sheet as formulas hence i want the moment auto insert takes place the formula of above row should get autofill in the new row which is not happening totally if i follow the VBA code mentioned , the rows are getting inserted in all my sheets the formula also shows up but it misses one row in it. Eg. I have sheet A with 100 rows , sheet B with 100 ros and so on with other sheets. If I insert row in sheet A it shows auto insert in Sheet B , but sheet B values of some column are linked with sheet A and the moment i insert a row eg 10 , row 11 is a new row and the same row 11 in sheet B also shows up but the value it should pull from row 11 of sheet A from column C rather it shows row 12 of sheet A , this happens on any row i insert the value in sheet B is always of the next row rather than same row that i inserted. Another problem that comes here is with my first column Sr. no the moment i insert a row in 5 , sr no 6 with new row shows up but prior to that i already had row 6 as i have a formula for Sr. no A6+1 as my column starts from A6 and so on so how to sort this issue with 2 rows with same sr. no. This is very crucial sheet am working on and i need to get this sorted, i have refereed many VBA codes but none is effective. Hoping for a proper solution to my issue.
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations