VBA Insert Row - fails sometimes

[Rijnsent]

I have a bit of code that has been working fine for years, but now suddenly get feedback from some users that my error catching shows them "Insert method of range class failed". The simplified version of my code is the following:

Sub AddLine()

On Error GoTo ErrCatch
Set Sht = Worksheets("SHEET_A")
Sht.Unprotect Password:="PWD"
Rw = ActiveCell.Row 
Set FirstCl = Sht.Cells(Rw, 1)
FirstCl.Rows("1:1").EntireRow.Copy
FirstCl.Rows("1:1").EntireRow.Insert Shift:=xlDown

Exit Sub
ErrCatch:
       Debug.Print Err.source, Err.Description
End Sub

As I wrote, it seems like it works fine for almost all users, but recently some bumped into this issue. It seems like the .Insert line sometimes fails, but I can't see what might cause it, as it does always work on the 2 test-laptops where me and my colleague work on. Does anyone have a pointer?

 

[Joe4]

Does it give you a debug option?
If so, which line of code does it highlight when you click it?

On the users where it is failing, if they click CTRL+END, what cell does Excel go to?
If it goes to the very last possible row in Excel, then it thinks they are already using all the rows. So it wouldn't allow you to insert any new ones.
This happens sometimes if people format or populate an entire column on their sheet (so Excel thinks all rows are being used).

 

[RoryA]

There is a recent (end of May release) bug that seems to be affecting quite a few people with inserting/deleting rows, so it may be related. Are you all on the same build?

 

[Rijnsent]

Hi @Joe4,
well, my VBA-code is locked, so if my user bumps into that warning I can't see where the code stopped (the debug is unavailable), but I can see the warning. My hunch is that it's the .insert line causing the problems because of the error message "Insert method of range class failed" (and there is no other line in my code inserting something). The weird thing: the same Excel file (with this code) does give that error on one PC, but won't give the error on another. The ActiveCell.Row gets checked for being <2000, so this macro won't be called from the last row of the sheet.

The sheet itself is locked and only about 2k rows are in use, but formatting might indeed run to the bottom of the sheet. But your idea of Excel thinking that all rows are used does sound plausible, I've seen that before. Is there a VBA-way to check and/or correct that?

The users are on different builds of Excel/Office/Windows, so @RoryA 's idea that it has to do with the end of May release sounds like the most logical suspect. @RoryA Is there a way to follow these updates and the new issues they cause? (It's not the first time this year that we bump into some update of Excel that bugs out some parts of our code.)

Cheers,
Koen

 

[RoryA]

Not specifically - I only know about this because of posts I've seen in forums.

 

[Rijnsent]

https://learn.microsoft.com/en-us/officeupdates/current-channel#version-2405-june-19

Version 2405: June 19 Version 2405 (Build 17628.20164)
Resolved issues - Excel
This fix rectifies a problem in Excel where inserting or deleting cells might occasionally fail, leading to formulas recalculating inaccurately.