Workbook taking forever to load ...

[adambc]

... my WB has a table that has 30 columns and just over 1000 rows - it also has some (internal to the WB) IFs and XLOOKUPs (but nothing that complicated!) - but is taking a long time to open, every time ...

In a copy I have stripped out all the Worksheets other than the WS containing the main table, removed every formula, got rid of all data validation, removed conditional formatting, etc, etc - but it's still taking a long time to open, every time!

Can anyone help me get to the bottom of this?

I've tried Inquire but it just hangs Excel when trying to clean up ...

The table is correctly sized ie only to the last row/column in the table ...

And I'm out of ideas ...

Thanks ...

PS have just copied/pasted as values the table to a new WB - MUCH smaller file size and opens in seconds - so I also copied/pasted the table to a new WB and the same thing ... it's taking a long time to open, every time! - there's something in the table which I haven't found yet?!!!

 

[rlv01]

Paste the following....

I love the name of that sub and I'm stealing it for my snippet lib.

 

[adambc]

Make a COPY of your workbook.

Paste the following in a regular module in the COPY and run it.

Compare the size of the COPY after running the macro and the size of the original. Has the COPY been reduced in size substantially, does the COPY run better than the Original ?

Sub LipoSuction()
'JBeaucaire (8/3/2009)
Dim LR As Long, LC As Long
Dim ws As Worksheet

For Each ws In Worksheets
    LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row + 1
    LC = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1

    'Clear everything below column A last cell and row 1 last cell
        ws.Range(ws.Cells(1, LC), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Delete
        ws.Range(ws.Cells(LR, 1), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Delete
Next ws

End Sub

@Logit

Had to delete a number (less than 10) of Pivot Tables before the macro would run right through and amend LC to use row 6 (the table header - rows 1 to 5 are control data), but when it did, file size reduced from 4844 KB to 4555 KB and still very slow/juddering!!!

Thanks for the code though - will store for future use ...

 

[Logit]

Ok ... so having a bloated workbook is not the issue. If it were bloated it would have been reduced in size to maybe 1/4 from 4844.

Have you tried loading the workbook and repairing it ?

 

[adambc]

Ok ... so having a bloated workbook is not the issue. If it were bloated it would have been reduced in size to maybe 1/4 from 4844.

Have you tried loading the workbook and repairing it ?

@Logit

By “loading the workbook and repairing it” do you mean using Inquire - if yes, that hangs Excel! - if no, how (never had the need to repair a WB before?

Thanks …

 

[rlv01]

1. If you disable calculation as a test, does that change load time?
2. To rule out PC issues (memory resources, etc), have you opened the workbook using a different PC?

 

[adambc]

1. If you disable calculation as a test, does that change load time?
2. To rule out PC issues (memory resources, etc), have you opened the workbook using a different PC?

@rlv01

Will test 1. this evening

2. Has been tried on several devices, all highly spec’d - all the same!

 

[Logit]

Although this resource talks about a file that cannot be opened ... attempting to repair your file may still be an option using these steps. As before, do this on a copy of your workbook first.

Another resource :

Microsoft recommends the following : https://support.microsoft.com/en-us/office/excel-not-responding-hangs-freezes-or-stops-working-37e7d3c9-9e84-40bf-a805-4ca6853a1ff4


After several attempts to repair a corrupted workbook I'll finally use the installation CD to repair the installation of Excel. If that doesn't produce a successful conclusion I'll resort to uninstalling Excel completely, reboot the computer and reinstall Excel.

I've also, on a few occasions, had to completely rewrite the workbook from scratch (no copy and paste from the corrupted workbook).

 

[adambc]

1. If you disable calculation as a test, does that change load time?
2. To rule out PC issues (memory resources, etc), have you opened the workbook using a different PC?

@rlv01

Disabling calculation made no difference!!!

Thanks again anyway - I think it's a rebuild!!!

 

[adambc]

Although this resource talks about a file that cannot be opened ... attempting to repair your file may still be an option using these steps. As before, do this on a copy of your workbook first.

Another resource :

Microsoft recommends the following : https://support.microsoft.com/en-us/office/excel-not-responding-hangs-freezes-or-stops-working-37e7d3c9-9e84-40bf-a805-4ca6853a1ff4


After several attempts to repair a corrupted workbook I'll finally use the installation CD to repair the installation of Excel. If that doesn't produce a successful conclusion I'll resort to uninstalling Excel completely, reboot the computer and reinstall Excel.

I've also, on a few occasions, had to completely rewrite the workbook from scratch (no copy and paste from the corrupted workbook).

@Logit

The repair log didn't tell me much, but the file size increased!!!

Thanks again anyway ...

 

[Logit]

I wouldn't expect the file size to increase while attempting to repair the workbook.

Strange.