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?!!!

 

[arthurbr]

Perhaps have a look at Fixing bloated file size and slow calculation in Excel under " confused range"

 

[adambc]

Perhaps have a look at Fixing bloated file size and slow calculation in Excel under " confused range"

@arthurbr

Thanks for this ...

Unfortunately I've been through the issues/solutions systematically and none of them apply to my WB ...

Still looking for suggestions on what I can do to get to the bottom of this?

 

[Logit]

Post a copy of your workbook for download.

 

[adambc]

Post a copy of your workbook for download.

I would love to, but my “work” is voluntary for a local charitable organisation and the data contains personal information that would be impossible to redact without either masking the issue, or maybe even exacerbating it!!!

Thanks anyway …

 

[Logit]

Lots of folks have received assistance here by removing the private/personal information and providing just enough markup data to make the
workbook function.

 

[rlv01]

1. Does your workbook contain any macros?
2. Does your install of Excel use any add-ins?
3. If you start excel in "safe" mode and then open the file, is it still slow?
(2 & 3 are related).

Ref:

How to Open Excel in Safe Mode - Zebra BI

Learn how to open Excel in safe mode with our step-by-step guide.

zebrabi.com

 

[adambc]

1. Does your workbook contain any macros?
2. Does your install of Excel use any add-ins?
3. If you start excel in "safe" mode and then open the file, is it still slow?
(2 & 3 are related).

Ref:

How to Open Excel in Safe Mode - Zebra BI

Learn how to open Excel in safe mode with our step-by-step guide.

zebrabi.com

Apologies for the delay ion responding - took some time off at short notice ...

1. No macros (xlsx file)
2. No Excel add-ins
3. No quicker to open and moving around still very jumpy in safe mode

Ctrl-End takes me to the end of the table ...

Can't see any of the usual suspects ...

And the table simply won't copy to a new WB to let me do some playing around without affecting the data!

Aaarrrggghhh!!!

Thanks for trying ...

 

[RoryA]

If you clear the formulas out of the table, is it still slow? What is the file size of a workbook with just that table in it?

Edit: also, if you clear all the cell contents in the table, is it still slow to open?

 

[Logit]

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