Excel File Tab freezes

[camanokid]

Running Windows 10 & Office 2010.
1. Most of my Excel workbooks work fine...when no macros used.
2. My "budget" workbook has slow performance & freeze issues...it uses macros.
3. Some/most slowness is due to many imbedded, conditional formulas in 12 sheets.
4. Open & close workbook is slow.
5. But my big concern is that Excel freezes when I select the File Tab...I have to force close Excel.
6. I use a Personal.xlsb file. I don't really need it since I have only one workbook using macros.
7. I have tried the Office Repair feature. I have NOT reinstalled Office.
8. I have tried MANY fixes (which work for other users) such as Safe Start/Delete addins/Changing Trust Center options/etc.
9. I'm left to focus on the macro issue.
10. Could it be the Personal.xlsb file? Should I stop using it? How can I go back to imbedded macros in the workbook?
11. I had this issue before Windows 10. I have struggled with this for many years.

Thanks for any help.

 

[igold]

Hi camanokid, welcome to the MrExcel Forum,

The first thing I would do is look for Phantom Ranges. On each sheet of your workbook, first hit CTRL+Home to take you to the first cell in the sheet. Then hit CTRL+End to take you to the cell that Excel thinks is the last cell on the sheet with data/formula. I am guessing you are going to find that the last cell on some sheets are way out of whack. If you do find any you will need to get that last cell to its proper position.

These Phantom Ranges bloat your Workbook and can slow everything down to a crawl from just navigating to running Macros. Even opening and closing the Workbook can become a chore.

You may want to Google the topic for some more information and different methods for deleting the phantom ranges, they can be stubborn sometimes.

 

[camanokid]

Thankyou.
I started an extensive REPLY...but somehow lost my window!?!
In summary, I have tried many methods to: show hidden cells/rows; reset last-active-cell; Delete phantom rows; VBA macro to reset last cell:
Sub ResetLastCell()
ActiveSheet.UsedRange
End Sub
etc. etc. - nothing has worked.

I have 4 sheets with some (not excessive) numbers of phantom rows.
e.g. last-active-cell = AP121; actual last-cell-with data = B57

Am I on the right track?
Why can't I correctly set the last-active-cell?

****

 

[igold]

Opinions vary. I have found that (to use your example here), If you delete all the extra columns manually from say D:AP, then ONLY immediately save the book and exit. Do not do anything else, do not navigate to another cell do not unhighlight anything just save and close. When you re-open see if that has reset your last cell.

 

[camanokid]

I have found some relief!
Using this procedure, I have solved the last-active-cell issue on 3 of my 4 problem sheets.
1. Hide Phantom Rows
a. CTRL+Shift+Down arrow on the last good row +1
b. Format/Hide Rows
2. Show Last Active Cell
a. Ctrl+End will show the correct cell
3. Unhide Phantom Rows
a. Select Entire Sheet
b. Format/Unhide Rows
4. Show Last Active Cell
a. Ctrl+End will show the correct cell !!

This procedure has improved Excel performance (reduced CPU usage).
The 3 sheets now behave closer to normal.

The 4th sheet has improved, but still has some issues.
Ctrl+End works OK
Ctrl+Home is slow (uses 15-23% CPU)

As far as my original problem is concerned...Excel still freezes (23-30% CPU) when selecting the File Tab.

More monitoring needed.

 

[igold]

Out of curiosity, how big (file size) is your workbook...

 

[camanokid]

Out of curiosity, how big (file size) is your workbook...

It is currently 8.31 MB.

In 2012 it was 3.2 MB.
I opened the 2012 file. This was the 1st year I used macros. Scrolling is very smooth and CPU usage is minimal.
But when I tried the File Tab, CPU went to 25% and hung for a few moments.!!
However after hanging, it did reveal the File Menu and I can select from the menu e.g. File/Options (a little sluggish but ok)

Over time, the file has gradually increase in size...no exact explanation.

 

[camanokid]

It is currently 8.31 MB.

In 2012 it was 3.2 MB.
I opened the 2012 file. This was the 1st year I used macros. Scrolling is very smooth and CPU usage is minimal.
But when I tried the File Tab, CPU went to 25% and hung for a few moments.!!
However after hanging, it did reveal the File Menu and I can select from the menu e.g. File/Options (a little sluggish but ok)

Over time, the file has gradually increase in size...no exact explanation.

Here is a image of my directory

 

[camanokid]

Here is a image of my directory

View attachment 58713

More info
I created a new blank sheet.
I copied the active area from my problem sheet... No issue with the COPY.
I then tried to PASTE into the new blank sheet.
Excel froze at 25% CPU. Paste never completed.
I had to force kill Excel.

 

[igold]

Does this get you a copy of the problem sheet into a new book...

Sub Copy()
    ActiveSheet.Copy
End Sub