1

This has been asked already, but none of the answers available helps me. I am trying to add a row to a small worksheet. I am allowed to add the row with Alt-I, R manually, but if I try to do it from a macro, I get this:

Error message when attempting to add rows from macro

I have tried, without effect, the following suggestions I have found on the Internet:

  1. Check that data isn’t ridiculously long. Ctrl-End takes me to G40. The last available row is 1048576.
  2. Unfreeze panes.
  3. Execute “ActiveSheet.UsedRange” in the Immediate window.
  4. Unmerge cells in row above the one I was inserting.
  5. Rows("1048500:1048576").Delete. This ought to free up 76 rows, yet immediately after it attempting to insert just one row is forbidden.
  6. Application.CutCopyMode = False
  7. Selecting all the rows below those used and choosing “Clear Content”, save, close and reopen.

I am using Excel 2016. The only solution that looks at all plausible is using Application.SendKeys to do Alt-I, R, but I would rather not do that if I can help it. Neither the sheet nor the workbook containing it is protected. If you want to know what the offending code is:

For iWorksheetCounter = 2 To wbkFinal.Worksheets.Count
    Set wksPartial = wbkFinal.Worksheets(iWorksheetCounter)
    lngCurrentRow = iWorksheetCounter + iRowOffset ' iRowOffset = 3
    wksTotals.Rows.Insert (lngCurrentRow + 1) ' this is not allowed for a reason I don't understand
    wksTotals.Cells(lngCurrentRow, 1).Value = wksPartial.Name
Next ' iWorksheetCounter
Stephen
  • 61
  • 1
  • 6
  • What do you have in lngCurrentRow ? – Alex Martinez Feb 15 '18 at 15:50
  • It's in a loop. It starts at 5. – Stephen Feb 15 '18 at 15:59
  • 1
    You need to show at least the entire loop. – Scott Craner Feb 15 '18 at 16:14
  • Where does it finishes? Why don't show more code? – Alex Martinez Feb 15 '18 at 16:47
  • The rest of the loop either executes without problems, or isn't executed because of the bug, but here it is: Set wksTotals = wbkFinal.Worksheets(1) For iWorksheetCounter = 2 To wbkFinal.Worksheets.Count Set wksPartial = wbkFinal.Worksheets(iWorksheetCounter) lngCurrentRow = iWorksheetCounter + iRowOffset ' iRowOffset = 3 wksTotals.Rows.Insert (lngCurrentRow + 1) ' this is not allowed for a reason I don't understand wksTotals.Cells(lngCurrentRow, 1).Value = wksPartial.Name Next – Stephen Feb 15 '18 at 16:51
  • 1
    Code goes in the question - it's unreadable in a comment – Tim Williams Feb 15 '18 at 17:20
  • This is not Allow because you are trying to insert a row after the last row in the sheet: wksTotals.Rows. returns the last row in the sheet, and in that last sheet, you are inserting more rows. Excel can't handle that. – Alex Martinez Feb 15 '18 at 19:32
  • In what row are you trying to insert the new row? – Alex Martinez Feb 15 '18 at 19:34
  • Typically row 5. Anyway, the answer is that "wksTotals.Rows.Insert (lngCurrentRow + 1)" should have been "wksTotals.Rows(lngCurrentRow + 1).Insert", for which I thank Alex very much. Excel is now happily inserting rows after row 5 with no error messages. – Stephen Feb 15 '18 at 19:59
  • Happy to help buddy – Alex Martinez Feb 15 '18 at 20:02

0 Answers0