Excel File Tab freezes

camanokid

New Member
Joined
Feb 23, 2022
Messages
6
Office Version
  1. 2010
Platform
  1. Windows
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.
 
Is there a lot of conditional formatting in the workbook?

I will provide some code I have compiled over the years to reduce the bloating that was discussed here. Open the workbook, add the macro to a module, run the code, save the file, close the file.

Check the file size of the file after the saving/close to see if the file size was reduced. Reopen workbook and see if it performs any better.

VBA Code:
Sub SheetFileSizeReducerV2()
'
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
'
    Dim ShapeTopLeftCellRow         As Long
    Dim ShapeTopLeftCellColumn      As Long
    Dim LastColumn                  As Long
    Dim LastRow                     As Long
    Dim LastColumnRowAddressFormula As Long
    Dim LastColumnRowAddressValue   As Long
    Dim LastRowColumnAddressFormula As Long
    Dim LastRowColumnAddressValue   As Long
    Dim Shp                         As Shape
    Dim ws                          As Worksheet
'
    For Each ws In Worksheets
        With ws
            On Error Resume Next
            LastColumnRowAddressFormula = .Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column      ' Address of Last row cell in Last Column
            LastColumnRowAddressValue = .Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column          ' Address of Last row cell in Last Column
            LastRowColumnAddressFormula = .Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row            ' Address of Last column cell in Last Row
            LastRowColumnAddressValue = .Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row                ' Address of Last column cell in Last Row
            On Error GoTo 0
'
'-----------------------------------------------------------------------------------------------
'
'           Determine the last column
            LastColumn = LastColumnRowAddressFormula                                                            ' returns Last Column #
'
            If LastColumnRowAddressValue <> 0 Then
                LastColumn = Application.WorksheetFunction.Max(LastColumn, LastColumnRowAddressValue)           ' returns Last Column #
            End If
'
'-----------------------------------------------------------------------------------------------
'
'           Determine the last row
            LastRow = LastRowColumnAddressFormula                                                               '
'
            If LastRowColumnAddressValue <> 0 Then
                LastRow = Application.WorksheetFunction.Max(LastRow, LastRowColumnAddressValue)                 '
            End If
'
'-----------------------------------------------------------------------------------------------
'
'           Determine if any shapes are beyond the last row and last column
            For Each Shp In .Shapes
                ShapeTopLeftCellRow = 0
                ShapeTopLeftCellColumn = 0
'
                On Error Resume Next
                ShapeTopLeftCellRow = Shp.TopLeftCell.Row
                ShapeTopLeftCellColumn = Shp.TopLeftCell.Column
                On Error GoTo 0
'
                If ShapeTopLeftCellRow > 0 And ShapeTopLeftCellColumn > 0 Then
                    Do Until .Cells(ShapeTopLeftCellRow, ShapeTopLeftCellColumn).Top > Shp.Top + Shp.Height
                        ShapeTopLeftCellRow = ShapeTopLeftCellRow + 1
                    Loop
'
                    If ShapeTopLeftCellRow > LastRow Then
                        LastRow = ShapeTopLeftCellRow
                    End If
'
                    Do Until .Cells(ShapeTopLeftCellRow, ShapeTopLeftCellColumn).Left > Shp.Left + Shp.Width
                        ShapeTopLeftCellColumn = ShapeTopLeftCellColumn + 1
                    Loop
'
                    If ShapeTopLeftCellColumn > LastColumn Then
                        LastColumn = ShapeTopLeftCellColumn
                    End If
                End If
            Next
'
'-----------------------------------------------------------------------------------------------
'
            .Range(.Cells(1, LastColumn + 1), .Cells(.Rows.Count, .Columns.Count)).EntireColumn.Delete
            .Range("A" & LastRow + 1 & ":A" & .Rows.Count).EntireRow.Delete
        End With
    Next
     
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
'
Msgbox "Completed!"
End Sub
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Can I throw in a couple of additional items:
1) on your problem sheet I know you have played around with row formatting and hide/unhide but have you specifically tried this:
[B]activesheet.usedrange.rowheight = 23[/B]

2) You are on a fairly old version of Excel.
Under Home > Cell Styles - Do you see the Normal style near the top or do you have a Squillion Accent Custom styles appearing ?
 
Upvote 0

Forum statistics

Threads
1,223,958
Messages
6,175,629
Members
452,661
Latest member
Nonhle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top