Inserting New Excel Row with VBA too many times results in Error 1004: Insert method of Range class failed

Question

This code has been working great. Then it just stopped working. If I remove a row it works once and then fails again after that point:

The 1004 Error occurs on this line below in the code when "the threshold" is reached whatever is causing it:

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Here's the Excel VBA Macro:

 Sub InsertNewRow()
    '
    ' InsertNewRow Macro
    ' Inserts a new row at row 2 and adds the date/time and user name.
    '
    ' Keyboard Shortcut: Ctrl+Shift+N
    '
        ActiveSheet.Rows("2:2").Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        ActiveSheet.Range("H2").Select
        ActiveCell.FormulaR1C1 = Now()
        ActiveSheet.Range("J2").Select
        ActiveCell.FormulaR1C1 = UCase(Application.UserName & "")
        ActiveSheet.Range("A2").Select
    End Sub

Any idea on WHY this is happening and how it can be prevented permanently?

MORE INFORMATION:

The data is in a table in the sheet. If I simply select a row and then attempt to manually insert a row, the INSERT option is greyed out.

If I select a table row in its entirety and then attempt to insert a table row, the option to add rows to the table is also greyed out. It seems to be a limitation of Excel formatted tables. I'm not sure why it cannot handle below 6000 rows though. It seems buggy.

I tried this to see if it was a limit of the table itself:

...Received this error:

Hi, Why don't you just use `Rows(2).Insert shift:=xlShiftDown`. — p77u77n77k, Oct 11 '19 at 05:23
Regarding why not just Rows(2)... because my experience with VBA tells me that selecting the cells tends to be much more reliable than manipulating them when they're not selected. VBA tends to be more buggy that way. But clearly this is the other way around this time. — EricI, Oct 11 '19 at 05:32
I deleted all the blank rows in the spreadsheet from the bottom and now I'm not able to replicate it after inserting 400 rows with the code. It's fixed for now, but I would really like to understand WHY it broke so I could prevent it for the future. It seems that some threshold was reached and that caused it, but there were only 4000 total rows in the spreadsheet, so it wasn't some 40K plus rows situation. — EricI, Oct 11 '19 at 05:35
I used a backup copy that still had the issue and used the "Rows(2).Insert shift:=xlShiftDown" code instead of my line 2 and it still produced the same 1004 error. — EricI, Oct 11 '19 at 05:44
Even though there may not have been data in the last row, there may have been some "formatting" i.e. if that last row had been touched at any point, there might have been an artifact tied to it. Fill the last cell with a colour e.g. and it'll give the same 1004 error. — jamheadart, Oct 11 '19 at 05:53
Also: Office 365/Excel is the Version of Excel this is occurring in. — EricI, Oct 11 '19 at 05:55
_because my experience with VBA tells me that selecting the cells tends to be much more reliable than manipulating them when they're not selected_ I have never seen anything to support that claim. It is quite opposite - avoiding selects makes the code cleaner, much faster and more reliable. — BrakNicku, Oct 11 '19 at 06:09
Manipulating a sheet that is not selected has been an issue, at least historically. — EricI, Oct 11 '19 at 06:38
@EricI Only if you try to `.Select` cells on those unactivated sheets, which can lead to errors. In any other case you can realiably manipulate sheets that are not active, you just have to do it right. — riskypenguin, Oct 11 '19 at 07:08
Most likely reason this occurs is the `UsedRange` became extended down to the last row in the sheet. If it happens again, run `Debug.Print ActiveSheet.Usedrange.Address` to test this. — chris neilsen, Oct 11 '19 at 07:13
Re _Manipulating a sheet that is not selected has been an issue, at least historically_ most common cause of this is implicit ActiveSheet references ( range references without an explicit worksheet reference). — chris neilsen, Oct 11 '19 at 07:17
Using `Select` can be disastrous. It allows things to happen between the selection and the action. If you have scripts that run over a few seconds the user can be doing allsorts of stuff that might inject an action between your code lines. — jamheadart, Oct 11 '19 at 07:33
In the backup version that still has the issue, ?ActiveSheet.UsedRange.Address returns $A$1:$T$5156. That's not really conclusive as to why that would be an issue. — EricI, Oct 11 '19 at 07:49
@Ericl good information! That rules out Usedrange as the issue. Could you add a screenshot of the error msg popup? — chris neilsen, Oct 11 '19 at 08:05
I tried: ThisWorkbook.Sheets("Real Worksheet Name Here").Rows(2).Insert shift:=xlShiftDown and it resulted in the same 1004 error. It doesn't seem to be related to the worksheet reference or the active selections. — EricI, Oct 11 '19 at 08:25
ANOTHER CLUE: If I just select a row, right-click, I see that INSERT is greyed out. It's not the code. The sheet is NOT protected, so it's not protection that's the issue. — EricI, Oct 11 '19 at 08:30
I added some more print screens to the original post. It seems to be a bug/issue in Excel formatted tables. There's no documented limit of rows to the formatted tables that I can find. I would assume it would be the same limit as the rows in the spreadsheet. Certainly, if I keep strolling down, it keeps making the table bigger visually. — EricI, Oct 11 '19 at 09:04
@ericl the fact you have a Table on the sheet is very relevant. The last error you posted means something extends down to the bottom of the sheet. If it's not UsedRange maybe it's the table Databodyrange — chris neilsen, Oct 11 '19 at 09:34

score 0 · Answer 1 · answered Oct 11 '19 at 07:17

Here, try this:

Option Explicit 'force variable declaration
Sub InsertNewRow()

    'Lets assume your column with all the rows filled is H so if this is not correct, feel free to make the changes needed.
    'You need to avoid using .Select or .Activate and this is a way to do so:

    'Working with the With block will allow you to reference an object with just a dot. in this case a worksheet
    With ThisWorkbook.Sheets("YourSheet") 'fully quallify your ranges, ThisWorkbook means the workbook with the code and change YourSheet

        'Declare a variable to check the last row with data
        Dim LastRow As Long
        LastRow = .Cells(1, "H").End(xlDown).Row 'this could erase data if you have blank cells between

        'check if the last row with data is the last row for the sheet
        If LastRow = .Rows.Count Then
            MsgBox "Sorry, no more rows can be added to this sheet." 'if so, pop a warning
            Exit Sub
        Else
            .Rows(LastRow + 1 & ":" & .Rows.Count).Delete 'if not, delete all the rows below your last row to avoid problems
        End If

        .Rows(2).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        .Range("H2") = Now 'this will print a value, so it won't change every second
        .Range("J2") = UCase(Application.UserName & "")

    End With

        'There was no need to select anything in this code, your sheet could even be hidden and this will work

End Sub

And read this for further info on how to avoid using select or activate.

I cannot blindly delete rows. The users sometimes put blank rows in the data, so the code above wouldn't work reliably. I would be better off manually removing the rows occasionally than depending on this code above to never delete real data. Thanks for your input, though. — EricI, Oct 11 '19 at 08:28

Inserting New Excel Row with VBA too many times results in Error 1004: Insert method of Range class failed

1 Answers1