File size increase

Kentman

Active Member
Joined
Apr 26, 2010
Messages
257
On a spreadsheet that was 3.5mb a crash occured and now it is 43mb!

Is there some way I could reduce this?
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,391
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
With the workbook open, try running the ResetAllUsedRanges macro below:
Code:
Sub ResetAllUsedRanges()
   Dim wks As Worksheet
   For Each wks In ActiveWorkbook.Worksheets
      ResetUsedRange wks
   Next wks
End Sub
Sub ResetUsedRange(Optional wks As Worksheet)
   Dim lngLastRow As Long, lngLastCol As Long, lngRealLastRow As Long, lngRealLastCol As Long
   On Error Resume Next
   If wks Is Nothing Then Set wks = ActiveSheet
   With wks
      With .Range("A1").SpecialCells(xlCellTypeLastCell)
         lngLastRow = .Row
         lngLastCol = .Column
      End With
      lngRealLastRow = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
      lngRealLastCol = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column
      If lngRealLastRow < lngLastRow Then .Range(.Cells(lngRealLastRow + 1, 1), .Cells(lngLastRow, 1)).EntireRow.Delete
      If lngRealLastCol < lngLastCol Then .Range(.Cells(1, lngRealLastCol + 1), .Cells(1, lngLastCol)).EntireColumn.Delete
      Debug.Print .UsedRange.Count
   End With
End Sub
 

Kentman

Active Member
Joined
Apr 26, 2010
Messages
257
Rory,

What I've found is that with my 16 sheets selected I inserted a new column whereby excel gave me a "I haven't got the resources to do that mate!" error but did it anyway. On saving the workbook the file increased 10 fold - your code didn't have much effect on that size (a couple of mb) but thank you anyway.

Any ideas why the error should increase the workbook this much? I can only think that it is writing loads of error crap into the excel workbook program code!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,391
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
What's in the sheets? Does the same thing happen if you insert the column in one sheet at a time?
 

Kentman

Active Member
Joined
Apr 26, 2010
Messages
257

ADVERTISEMENT

I think I've found the problem - there is actually nothing in any of the sheets but I do have this in each sheets module:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 1 Then Target.Offset(, 29).Value = Now
If Target.Column = 2 Then Target.Offset(, 29).Value = Now
If Target.Column = 3 Then Target.Offset(, 29).Value = Now....etc

Which repeats for 21 cells so I think this is the resource issue - I've just deleted the code from all the sheet modules and it doesn't throw up an error when I add the columns over multiple sheets - so it may be cured, hopefully!

I know it's not a good idea to have all this code tracking changes but the system is for the Police who require evidence of data change and it's the only way I know how to do it!
Luckily I keep backup copies after major changes so can role back.

Thanks for your help Rory.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,391
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You could probably get round that using:
Code:
If Target.Count >= Rows.Count then exit sub
 

Kentman

Active Member
Joined
Apr 26, 2010
Messages
257

ADVERTISEMENT

Sorry Rory

How would I use that in terms of the worksheet code I posted - not that great on this sort of thing as am still learning from you guys :)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,391
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You would add it as the first line of the change event.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,642
Office Version
2019
Platform
Windows
I know it's not a good idea to have all this code tracking changes but the system is for the Police who require evidence of data change and it's the only way I know how to do it!
Is setting the workbook as shared an option?

In excel 07, Review > Share workbook

Doing this excel will track all user made changes to the workbook, which can be reviewed using Track Changes > Highlight Changes, then setting the options to list the history as desired.

edit: Tried to post an example but the board was showing the html code instead of the sheet layout.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,297
Messages
5,510,466
Members
408,791
Latest member
bwirth

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top