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.

 

[johnnyL]

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.

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

 

[Alex Blakenburg]

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 ?