Excel File - Doesn't like opening

[tlc53]

Hi there,

I have been working on an Excel file which has grown in size, with various VBA codes/macros.

Recently it has taken a lot longer to save which I assume is down to size - 15,615 KB. Is this considered big in Excel world?

Today though, I have had difficulty opening it. Most of the time it just shows the loading icon and then it eventually becomes unresponsive. On a couple of occasions it has opened but I can't work out any reason for it opening on these occasions and not on others. For example, I tried accessing the file through file explorer, I tried accessing through Excel and also the recent file option in Excel.

I am now accessing the file on a newer/better laptop, so I know it's a file problem. I really need for it to behave properly tomorrow. Any idea why I might be experiencing these problems?

Once the file is open, I am able to navigate and save without any problems. As soon as I close it though and try to re-open, problems again!

Thank you for your time!

 

[Logit]

.
Just off hand I would agree it is most likely the size of the workbook.

If you are storing a ton of data in your workbook, moving that to one of more other workbooks smaller in size would help greatly.

 

[tlc53]

Hmm.. not really what I wanted to hear but thanks

One of the positive benefits of this spreadsheet was that everything would be in one place/file, so you can easily navigate and flick between sheets. The file doesn't actually contain all the data yet. The file size, I guess, is down to all the VBA coding. Is it well known that VBA coding increases the file size greatly? If so, I guess one option would be to remove a lot of the VBA coding. Is there anything else I could try, rather than split the file? What is a safe maximum size Excel file?

This spreadsheet isn't even complete yet so it's only going to get bigger, so I need to think about how I'm going to resolve this. Is there a way to see what KB's are associated with each sheet or perhaps VBA coding action? Grasping at straws here..

 

[Logit]

.
First thing I would do is save the file as .XLSB

That should shave off 1/4 to 1/3 the file size.


The other thing is to check if there is "ghost data" existing on your data sheets. Paste this macro into a MODULE and run it.
The macro will check all sheets in the workbook looking for the "ghost data" and delete it. Sometimes EXCEL will save
unseen data in cells below your visualized 'used range'. Each time you run your macro/s it adds to the rows of this
unseen data which results in workbook bloat.

MAKE A COPY OF YOUR EXISTING WORKBOOK ... TRY THE ABOVE STEPS ON THE COPY FIRST. Make certain it works
successfully prior to taking those steps on the original.

Here is the macro :

Option Explicit


Sub ExcelDiet()


Dim j               As Long
Dim k               As Long
Dim LastRow         As Long
Dim LastCol         As Long
Dim ColFormula      As Range
Dim RowFormula      As Range
Dim ColValue        As Range
Dim RowValue        As Range
Dim Shp             As Shape
Dim ws              As Worksheet


    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
        
    On Error Resume Next


    For Each ws In Worksheets
        With ws
            'Find the last used cell with a formula and value
            'Search by Columns and Rows
            On Error Resume Next
            Set ColFormula = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
            Set ColValue = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
                LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
            Set RowFormula = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
            Set RowValue = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
            On Error GoTo 0
                    
            'Determine the last column
            If ColFormula Is Nothing Then
                LastCol = 0
            Else
                LastCol = ColFormula.Column
            End If
            If Not ColValue Is Nothing Then
                LastCol = Application.WorksheetFunction.Max(LastCol, ColValue.Column)
            End If
                            
            'Determine the last row
            If RowFormula Is Nothing Then
                LastRow = 0
            Else
                LastRow = RowFormula.Row
            End If
            If Not RowValue Is Nothing Then
                LastRow = Application.WorksheetFunction.Max(LastRow, RowValue.Row)
            End If
                
            'Determine if any shapes are beyond the last row and last column
            For Each Shp In .Shapes
                j = 0
                k = 0
                On Error Resume Next
                j = Shp.TopLeftCell.Row
                k = Shp.TopLeftCell.Column
                On Error GoTo 0
                If j > 0 And k > 0 Then
                    Do Until .Cells(j, k).Top > Shp.Top + Shp.Height
                        j = j + 1
                    Loop
                    If j > LastRow Then
                        LastRow = j
                    End If
                    Do Until .Cells(j, k).Left > Shp.Left + Shp.Width
                        k = k + 1
                    Loop
                    If k > LastCol Then
                        LastCol = k
                    End If
                End If
            Next
                    
            .Range(.Cells(1, LastCol + 1), .Cells(.Rows.Count, .Columns.Count)).EntireColumn.Delete
            .Range("A" & LastRow + 1 & ":A" & .Rows.Count).EntireRow.Delete
        End With
    Next
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    MsgBox "Done."


End Sub

If you want to make absolutely certain all of the "ghost data" has been removed (if it exists) ... on each sheet
go to the bottom of the sheet (1,400 and some odd) and highlight the last few rows. Then with those rows still highlighted, go back
up to the first blank row below any existing data on that sheet. Hold down the SHIFT key and click on that row.
You have now highlighted all of what appear to be blank rows. Right click in the highlighted rows and then
click DELETE. Repeat the process for each sheet.