1

I have an Excel file with multiple sheets that are for the most part protected. The user can only edit certain cells and all adding/copying/duplicating/deleting of rows is handled via buttons and VBA.

The code for inserting a row:

ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.OffSet(1).Insert xlShiftDown

and deleting a row:

Selection.EntireRow.Select
Selection.Delete xlShiftUp

There's a large number of Sum(...) and other formulas in the Excel file. In general, those update correctly. Sum(B5:B10) correctly references Sum(B5:B15) when you add 5 rows to it.
Every now and then there seems to be a bug that corrupts a lot of the formulas and shifts them incorrectly (e.g. Sum(B4:B14)).

This happens occasionally and on specific PCs. I can't reproduce them on any of my machines.
On most of the User-PCs it works. On some machines it seems to appear more regularly, but still not reproducable on demand.

Most users report that it happens after they click the insert-row & delete-row buttons often in a short time.

Information from one user who encountered this problem:
Operating-System: Windows 10, 64-bit;
Excel-Version: Excel 2016, 32-bit

Excel had been opened from mail and was then saved on Disk.

Programs open at the same time problem occurred:
One other Excel file (without macros), Outlook, IE, Adobe, Skype

As far as I know there are no "automatic tools" like AutoHotKey or something along the lines installed.

Community
  • 1
  • 1
Tobias L
  • 11
  • 3
  • 2
    [Don't use `ActiveCell` or `Selection` or `Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – BigBen Mar 11 '20 at 13:48
  • @BigBen I think in this case it is warranted as the user chooses the row to add or delete then hits the button that call the sub. – Scott Craner Mar 11 '20 at 13:49
  • @ScottCraner - that's a good point. In any case, i don't think we'll be able to repro here, so it was just a guess on my part. – BigBen Mar 11 '20 at 13:51
  • You will need to modify your formula to not be linked to dynamic rows. you can anchor the first row by using `INDEX(B:B,5)` in place of `B5`. To get the last row you can use: `XLOOKUP` or `INDEX(MATCH())` or `LOOKUP` and between the two create a dynamic formula: `=SUM(INDEX(B:B,5):INDEX(B:B,MATCH(1E+99,B:B)))` – Scott Craner Mar 11 '20 at 13:55
  • *"Most users reported that it happens after they click the insert-row & delete-row buttons with the macros extremely often in a very short time."* - I wonder if this is somehow an Event issue, one Event not finishing before it starts again perhaps? More possible if Events are disabled during the run. – CLR Mar 11 '20 at 14:06
  • If your sub deletes a row which is a reference for a specific formula on the other row, this last row (not having a reference anymore) looses the reference in its formula. That "sporadically" means only that the deleted row does contain cells referenced by other cells on other rows. I am sure that inserting does not produce such an effect. Can you check this aspect? If I am right, your code maybe would be good to check all cells of the row if they have formulas... – FaneDuru Mar 11 '20 at 14:08
  • @FaneDuru: Thanks for the answer. The rows which are deleteable are only ever referenced as a range in a SUM or SUBTOTAL-formula. The cell containing this sum-formula gets deleted via a seperate function which does not seem to produce the problem. – Tobias L Mar 11 '20 at 14:26
  • @CLR: Thanks for the answer. I don't use any events in the VBA code myself. – Tobias L Mar 11 '20 at 14:27
  • @Scott Craner: Thanks for the answer. I will change the formula accordingly and give feedback, when I get response from the users. Is there any explanation why referencing dynamic rows can create this problem? And why this only happens so sporadically and seemingly only on specific pcs? – Tobias L Mar 11 '20 at 14:32
  • 1
    When deleting or inserting the first or last row of the reference can have some unintended consequences to the references. – Scott Craner Mar 11 '20 at 14:35
  • @Tobias L: I understand that you are sure that the specific rows loosing their references in formulas are not deleted by your above sample code. Apropos, it is not necessary to select the row before its deletion... But I am not so sure that you are right. Not sure neither you are... :) So, I would suggest you to analize the rows having cells with a lost reference and try to deduce where the reference in discussion should be. In this way, I think, you will be able to understand if the disappeared reference was (or not) on a deleted row, like I suppose... – FaneDuru Mar 11 '20 at 14:37

0 Answers0