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?
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,959
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. 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
36,959
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. 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
36,959
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. 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
36,959
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You would add it as the first line of the change event.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,968
Office Version
  1. 365
Platform
  1. 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,753
Messages
5,660,728
Members
418,591
Latest member
clayest94

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
Top