1. 選擇要自動填充公式的數據范圍，然後單擊 插入 > 表，看截圖：
2。 在 創建表格 對話框，檢查 我的表有標題 如果數據中有標題，請參閱截圖：
3。 然後點擊 OK 按鈕，表格已經創建好了，現在，當你插入一個空白行時，上面的公式會自動插入到新行中，詳見截圖：
1。 選擇包含要自動填充的公式的工作表選項卡，然後右鍵單擊以選擇 查看代碼 從上下文菜單中去 Microsoft Visual Basic for Applications 窗口，然後將以下代碼複製並粘貼到模塊中：
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
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 4 months agoExcellent simple piece of code!!!!
To post as a guest, your comment is unpublished.· 10 months agoHi,
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.
To post as a guest, your comment is unpublished.· 1 years agoI 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???
To post as a guest, your comment is unpublished.· 2 years agoDoes the table method work in excel for xp (2002) and if yes, how?
To post as a guest, your comment is unpublished.· 2 years agoThe code works very well, but does not update cell reference in first row after the inserted row.See below"
50 10 40
40 10 50
40 20 20
To post as a guest, your comment is unpublished.· 2 years agoThis 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?
50 10 40
40 10 50
40 20 20
To post as a guest, your comment is unpublished.· 2 years agoAbove formula is very helpful for me please tell me how to use this code permanently
To post as a guest, your comment is unpublished.· 2 years agodear 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
To post as a guest, your comment is unpublished.· 3 years agoI 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.