Problem adding rows and columns

[uncommonadvice]

When I try to add a row to a certain document I get the blue spinning wheel for 10 seconds and no new row is added. The sheet then freezes and eventually crashes.

When I later re-open the document the new row has appeared. The same thing arises when I try to add columns.

This seemed to start out of the blue a few weeks back. Research online has suggested removing rows from the bottom of the spreadsheet. I've tried that to no avail.

I'm using Microsoft 365. The sheet isn't big (2.5mb). There are no links to other spreadsheets.

Any ideas?

 

[Bren4242]

Unfortunately can't be certain without seeing the actual sheet. From my experience, I've had this happen in resource-heavy workbooks, where Excel is struggling to refresh and save. Recently had an issue where auto-save feature (with some user intervention I'm sure) had resulted in about 10,000 linked images being created on one sheet. The workbook wasn't big (about 5MB), but doing anything on the sheet resulted in a 20 second delay. In short, any other delays on your workbook while entering/modifying data?

 

[uncommonadvice]

Unfortunately can't be certain without seeing the actual sheet. From my experience, I've had this happen in resource-heavy workbooks, where Excel is struggling to refresh and save. Recently had an issue where auto-save feature (with some user intervention I'm sure) had resulted in about 10,000 linked images being created on one sheet. The workbook wasn't big (about 5MB), but doing anything on the sheet resulted in a 20 second delay. In short, any other delays on your workbook while entering/modifying data?

No other delays. Certainly nothing noticeable. Just adding rows and columns

 

[johnnyL]

Run the following macro when that sheet is active:

Sub TestExcelLastAddresses()
'
    Dim LastRow             As Long
    Dim ExpectedLastCell    As String, ExcelReportedLastCell    As String
    
    LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row                        ' Save Last used row
    ExpectedLastCell = "$" & Split(Cells(1, (Cells.Find("*", , xlFormulas, _
                    , xlByColumns, xlPrevious).Column)).Address, "$")(1) & LastRow      ' Save ExpectedLastCell
    ExcelReportedLastCell = Cells.SpecialCells(xlLastCell).Address(0, 1)                ' Save Ctrl+End equivalent to ExcelReportedLastCell
'
    Debug.Print ExpectedLastCell                                                        ' Display result to 'Immediate' window ... CTRL+G in VBE
    Debug.Print ExcelReportedLastCell                                                   ' Display result to 'Immediate' window ... CTRL+G in VBE
'
    MsgBox "The Expected last cell address = " & ExpectedLastCell & vbCrLf & _
            "The Excel Reported last cell address = " & ExcelReportedLastCell           ' Display results to a message box
End Sub

Report the results back here.

 

[uncommonadvice]

Run the following macro when that sheet is active:

Sub TestExcelLastAddresses()
'
    Dim LastRow             As Long
    Dim ExpectedLastCell    As String, ExcelReportedLastCell    As String
   
    LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row                        ' Save Last used row
    ExpectedLastCell = "$" & Split(Cells(1, (Cells.Find("*", , xlFormulas, _
                    , xlByColumns, xlPrevious).Column)).Address, "$")(1) & LastRow      ' Save ExpectedLastCell
    ExcelReportedLastCell = Cells.SpecialCells(xlLastCell).Address(0, 1)                ' Save Ctrl+End equivalent to ExcelReportedLastCell
'
    Debug.Print ExpectedLastCell                                                        ' Display result to 'Immediate' window ... CTRL+G in VBE
    Debug.Print ExcelReportedLastCell                                                   ' Display result to 'Immediate' window ... CTRL+G in VBE
'
    MsgBox "The Expected last cell address = " & ExpectedLastCell & vbCrLf & _
            "The Excel Reported last cell address = " & ExcelReportedLastCell           ' Display results to a message box
End Sub

Report the results back here.

 

[johnnyL]

That means that excel is seeing about twice the amount of rows of data that you have and some extra columns as well.

You should try deleting those extra columns and rows and then save the workbook.

 

[uncommonadvice]

The macro is now telling me that the expected end cell matches the reported end cell.......... but the problem persists.

Any ideas?

 

[uncommonadvice]

The macro is now telling me that the expected end cell matches the reported end cell.......... but the problem persists.

Any ideas?

I think I might have gotten to the bottom of this. I have just deleted all "Autoshapes" and the doc seems to be working fine now.