error msg when I try to insert rows

acerocha 

you are not the only one. This sucks because I have formulas setup that I now have to delete in order to insert rows and cells. This never happened before. When working with a large spreadsheet this is unacceptable.

acerocha 

The issue is likely due to the 'used range' encompassing the entire sheet.

If you're running an Enterprise (work) version of M365, then you can use 'Clean Excess Formatting':

Clean excess cell formatting on a worksheet - Microsoft Support

If Inquire is not available, select the row after your data ends, press ctrl+shift+down arrow to select to the bottom of the sheet, then right-click delete. Repeat this for the columns.

acerocha 

This error message usually occurs when Excel identifies non-empty cells or content (which may not be immediately visible) at the end of your worksheet, preventing the insertion of new rows. Here are a few steps you can take to resolve this issue:

1. Manually Check for Content:
• Scroll to the bottom of your worksheet and manually check for any content, even if it looks empty. This includes checking for any stray data, formatting, or formulas.
2. Clear Formats and Formulas:
• Select the last row with data.
• Right-click and choose "Clear Contents" to remove data.
• Then, right-click again and choose "Clear Formats" to remove any formatting.
• Repeat this for any other columns with data.
3. Use the Clear All Command:
• Instead of clearing contents and formats separately, you can use the "Clear All" command. Select the row with data, right-click, and choose "Clear All."
4. Check for Merged Cells:
• Merged cells, even if they appear empty, can cause issues. Unmerge any merged cells at the end of your data.
5. Check for Hidden Rows:
• Sometimes, rows may be hidden. Unhide all rows and then try to insert new rows.
6. Delete Blank Rows:
• Highlight blank rows, right-click, and choose "Delete."
7. Use a Macro:
• If the above steps do not work, you can use a VBA (Visual Basic for Applications) macro to clear the contents of the last row. Press Alt + F11 to open the VBA editor, insert a new module, and run a macro to clear the last row.

Example VBA code:

Sub ClearLastRow()
    Rows(Rows.Count).ClearContents
End Sub

After running the macro, try inserting new rows again.

Remember to save your work before making significant changes to your spreadsheet. If the issue persists, it might be a good idea to provide more information. Information about the excel version, operating system, storage medium, etc. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.