File size increase

[Kentman]

On a spreadsheet that was 3.5mb a crash occured and now it is 43mb!

Is there some way I could reduce this?

 

[RoryA]

With the workbook open, try running the ResetAllUsedRanges macro below:

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]

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]

What's in the sheets? Does the same thing happen if you insert the column in one sheet at a time?

 

[Kentman]

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]

You could probably get round that using:

If Target.Count >= Rows.Count then exit sub

 

[Kentman]

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]

You would add it as the first line of the change event.

 

[Kentman]

Thank you.

 

[jasonb75]

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.