I've got a 3 MB VBA file that has grown to 50 MB+. Any ideas on how to chop
it down?

Things tried already:
-Last cell issue resolved, including contexture fix.
-Temp files in Windows & Internet purged.

I've spent hours researching this on this site and others, but nothing seems
to workl

Thanks for any help,
John

Do you have any pictures or shapes that you don't need in there? Any
named ranges which are no longer used?

Pete

On Jul 8, 12:22 am,
wrote:
I've got a 3 MB VBA file that has grown to 50 MB+. Any ideas on how to chop
it down?

Things tried already:
-Last cell issue resolved, including contexture fix.
-Temp files in Windows & Internet purged.

I've spent hours researching this on this site and others, but nothing seems
to workl

Thanks for any help,
John

Pete, thanks for the response. no name ranges or shapes. I use Microsoft's
Excess Formatting Cleaner hopefully to clean up these things. John

"Pete_UK" wrote:

Do you have any pictures or shapes that you don't need in there? Any
named ranges which are no longer used?

Pete

On Jul 8, 12:22 am,
wrote:
I've got a 3 MB VBA file that has grown to 50 MB+. Any ideas on how to chop
it down?

Things tried already:
-Last cell issue resolved, including contexture fix.
-Temp files in Windows & Internet purged.

I've spent hours researching this on this site and others, but nothing seems
to workl

Thanks for any help,
John

Have you verified that resetting the UsedRange has worked by going to some
sheets and checking? If you do that and it turns out that it's not as
expected, you might look at this new info that Microsoft published about it
all along with what was available at Contextures:
http://office.microsoft.com/en-us/ex...CH100648141033


" wrote:

I've got a 3 MB VBA file that has grown to 50 MB+. Any ideas on how to chop
it down?

Things tried already:
-Last cell issue resolved, including contexture fix.
-Temp files in Windows & Internet purged.

I've spent hours researching this on this site and others, but nothing seems
to workl

Thanks for any help,
John

Pete wrote << Do you have any pictures or shapes that you don't need in
there? Any named ranges which are no longer used?

Similarly do you have any Pivot Tables which can be eliminated? Setting up
Pivot Tables afresh reduces the size of the file (I know not why)

Regards.

Bill Ridgeway
Computer Solutions

If you do have pivots, modify the pivot table settings so that it does
not save the underlying data with the table. This will easily cut the
file size in 1/2 if you have any large pivots.

"Tim879" wrote in message
oups.com...
If you do have pivots, modify the pivot table settings so that it does
not save the underlying data with the table. This will easily cut the
file size in 1/2 if you have any large pivots.

Can you do that in an existing pivot table or do you have to start over
please?

Bill Ridgeway

Bill, thanks for the response, but no pivot tables. I read a vague
description about Excel wanting to store equations....dunno. Any other
suggestions would be welcomed. John

"Bill Ridgeway" wrote:

"Tim879" wrote in message
oups.com...
If you do have pivots, modify the pivot table settings so that it does
not save the underlying data with the table. This will easily cut the
file size in 1/2 if you have any large pivots.

Can you do that in an existing pivot table or do you have to start over
please?

Bill Ridgeway

Tim, thanks for the response, but no pivot tables. I read a vague
description about Excel wanting to store equations....dunno. Any other
suggestions would be welcomed. John

"Tim879" wrote:

If you do have pivots, modify the pivot table settings so that it does
not save the underlying data with the table. This will easily cut the
file size in 1/2 if you have any large pivots.

JLatham, thanks for the try, but deleting (I assume this is what you mean by
'resetting') unused rows and columns didn't seem to help. As for
Contextures, I'm doing basically the same thing by using Microsoft's Add-In,
Excess Formatting Cleaner. Any other ideas would be appreciate. John

"JLatham" wrote:

Have you verified that resetting the UsedRange has worked by going to some
sheets and checking? If you do that and it turns out that it's not as
expected, you might look at this new info that Microsoft published about it
all along with what was available at Contextures:
http://office.microsoft.com/en-us/ex...CH100648141033


" wrote:

I've got a 3 MB VBA file that has grown to 50 MB+. Any ideas on how to chop
it down?

Things tried already:
-Last cell issue resolved, including contexture fix.
-Temp files in Windows & Internet purged.

I've spent hours researching this on this site and others, but nothing seems
to workl

Thanks for any help,
John

The default is to have "save data with table layout" ticked. What is the
practical difference between saving data and not saving data please.

Thanks.

Bill Ridgeway

Bill

If I understand the process correctly, saving data with your pivot
table actually makes a copy of your data and saves it with the pivot
table. For example if your pivot was based on a tab called data. After
creating your pivot, you could delete the data tab and still be able
to drill into your pivot table data since a copy of it was saved with
the pivot.

If you uncheck this option, a copy of the data is not saved, thus
making the file considerably smaller - especially if you work with
large data sets.

To find this option, right click on your pivot, go to table options -
Uncheck Save Data With Pivot Table Layout.

You could use the code below to cycle through all of your pivots in a
workbook and uncheck this option as well as refresh the pivots.

Sub Update_All_Pivots()
'Cycles through each page of a work book checking for pivots. Each
pivot found
'is refreshed

continue = MsgBox("This macro will update all pivots in the workbook.
Do you want to continue?", _
vbYesNo)

If continue = vbYes Then

Dim iSheets As Integer, x As Integer
Dim iPivot As Integer, _
strCurrentSheet As String

On Error GoTo Error_Found

'Count number of sheets in workbook
iSheets = ActiveWorkbook.Sheets.Count

'remember current sheet
strCurrentSheet = ActiveSheet.Name

If Windows.Count = 0 Then _
GoTo Exit_Update_All_Pivots


strResponse = MsgBox("Do you want to save data with pivot table
(larger file size)?", vbYesNo)

Application.ScreenUpdating = False

For x = 1 To iSheets

'go to a worksheet to change pivot tables
Sheets(x).Activate

'turn warning messages off
Application.DisplayAlerts = False

'change all pivot tables on
'this worksheet one at a time
For iPivot = 1 To ActiveSheet.PivotTables.Count
'ActiveSheet.PivotTables(iPivot).HasAutoFormat = False
ActiveSheet.PivotTables(iPivot).PivotCache.Refresh

If strResponse = vbYes Then
ActiveSheet.PivotTables(iPivot).SaveData = False
End If
Next

'turn warning messages on
Application.DisplayAlerts = True

Next

'return to worksheet that you were originally at
Application.ActiveWorkbook.Sheets(strCurrentSheet) .Activate

MsgBox ("Pivots updated successfully")

End If

GoTo Exit_Update_All_Pivots

Error_Found:
MsgBox ("Error Found. Macro ending.")
MsgBox Err & ": " & Error(Err)

Exit_Update_All_Pivots:
Application.CommandBars("PivotTable").Visible = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True

If continue = vbNo Then MsgBox ("Cancelled")

End Sub

"Tim879" wrote in message
ups.com...
Bill

If I understand the process correctly, saving data with your pivot
table actually makes a copy of your data and saves it with the pivot
table. For example if your pivot was based on a tab called data. After
creating your pivot, you could delete the data tab and still be able
to drill into your pivot table data since a copy of it was saved with
the pivot.

If you uncheck this option, a copy of the data is not saved, thus
making the file considerably smaller - especially if you work with
large data sets.

To find this option, right click on your pivot, go to table options -
Uncheck Save Data With Pivot Table Layout.

You could use the code below to cycle through all of your pivots in a
workbook and uncheck this option as well as refresh the pivots.

Sub Update_All_Pivots()
'Cycles through each page of a work book checking for pivots. Each
pivot found
'is refreshed

continue = MsgBox("This macro will update all pivots in the workbook.
Do you want to continue?", _
vbYesNo)

If continue = vbYes Then

Dim iSheets As Integer, x As Integer
Dim iPivot As Integer, _
strCurrentSheet As String

On Error GoTo Error_Found

'Count number of sheets in workbook
iSheets = ActiveWorkbook.Sheets.Count

'remember current sheet
strCurrentSheet = ActiveSheet.Name

If Windows.Count = 0 Then _
GoTo Exit_Update_All_Pivots


strResponse = MsgBox("Do you want to save data with pivot table
(larger file size)?", vbYesNo)

Application.ScreenUpdating = False

For x = 1 To iSheets

'go to a worksheet to change pivot tables
Sheets(x).Activate

'turn warning messages off
Application.DisplayAlerts = False

'change all pivot tables on
'this worksheet one at a time
For iPivot = 1 To ActiveSheet.PivotTables.Count
'ActiveSheet.PivotTables(iPivot).HasAutoFormat = False
ActiveSheet.PivotTables(iPivot).PivotCache.Refresh

If strResponse = vbYes Then
ActiveSheet.PivotTables(iPivot).SaveData = False
End If
Next

'turn warning messages on
Application.DisplayAlerts = True

Next

'return to worksheet that you were originally at
Application.ActiveWorkbook.Sheets(strCurrentSheet) .Activate

MsgBox ("Pivots updated successfully")

End If

GoTo Exit_Update_All_Pivots

Error_Found:
MsgBox ("Error Found. Macro ending.")
MsgBox Err & ": " & Error(Err)

Exit_Update_All_Pivots:
Application.CommandBars("PivotTable").Visible = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True

If continue = vbNo Then MsgBox ("Cancelled")

End Sub

Thanks Tim for that comprehensive reply.

It would seem that the effect of not ticking the "Save data with data
layout" is that data is lost. If, therefore, the primary data is not lost
(either the spreadsheet is deleted or the Pivot Table transferred to another
file) there seems to be no practical reason for ticking SDWDL.

Perhaps someone could confirm this.

Thanks.

Bill Ridgeway