Large Excel File after Macro Run (how to make the size smaller)

S

squeakums

Well-known Member
Joined
May 15, 2007
Messages
823
Office Version
  1. 365
Okay, so I have a large macro written that changes formatting in a file, the lines might only be like 300 lines and the columns maybe 8 columns, but the size of the file is rather large. How can I write it in the macro to dump extra space? I have the macro to create a new file and to move the excel tab to that file. Any help would be greatly appreciated.

Also, another note I wrote in the macro to delete any extra blank rows/columns to see if it would make the size smaller and it didn't do anything to the size.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Is the formatting that the macro does confined to the used range or does it format entire rows and/or entire columns? The latter may cause file bloat.
 
Upvote 0
Here is the entire macro, if you know of a way I can edit this let me know, thanks:
Code: 
    Sheets("Ariba Tree").Select
Range("A1").Select
ActiveCell.CurrentRegion.Select
    Selection.RowHeight = 12.75
    Range("A1").Select
    Columns("H:H").Select
    Selection.Delete Shift:=xlToLeft
    Selection.CurrentRegion.AutoFilter Field:=4, Criteria1:="="
    Rows("3:3").Select
    Range("B3").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.ShowAllData
    Range("A1").Select
    Columns("D:D").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("D:D").ColumnWidth = 19
    Range("E1").Select
    Range("A1:J1").Select
    Range("J1").Activate
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Master ID"
    Range("D1").Select
    Columns("K:K").Select
    Selection.Copy
    Columns("L:L").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "SW or NO"
    Columns("A:L").Select
    Range("L1").Activate
    Selection.AutoFilter
    Selection.AutoFilter
    Range("A2").Select
    ActiveWindow.FreezePanes = True
    Selection.CurrentRegion.AutoFilter Field:=9, Criteria1:= _
        "Master Agreement"
Range("D2").Formula = "=C2"
Range("D2", "D" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
    Range("D2").Select
    ActiveSheet.ShowAllData
    Columns("D:D").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D1").Select
    Selection.CurrentRegion.AutoFilter Field:=9, Criteria1:= _
        "Master Agreement"
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    ActiveSheet.ShowAllData
    Range("D1").Select
    Columns("A:L").Select
    Selection.ColumnWidth = 20.14
    Range("A2").Select
With Columns(4)
   .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
   .Value = .Value
End With
Range("A2").Select
    Selection.CurrentRegion.AutoFilter Field:=1, Criteria1:="="
    Rows("2:3000").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    ActiveSheet.ShowAllData
    Range("A1").Select
    Selection.CurrentRegion.AutoFilter Field:=9, Criteria1:= _
        "Master Agreement"
    Range("A2:K2").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    ActiveSheet.ShowAllData
    Range("A1").Select
    Columns("f:g").Select
    Selection.NumberFormat = "mm/dd/yy;@"
    Selection.CurrentRegion.Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Range("A2").Select
    Selection.ColumnWidth = 8
    Columns("A:A").Select
    Selection.ColumnWidth = 25
    Columns("K:K").Select
    Selection.ColumnWidth = 25
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("I:I").EntireColumn.AutoFit
    Range("A2").Select
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
        Columns("B:B").Select
    Selection.ColumnWidth = 8
    Range("C15").Select
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("E:H").Select
    Selection.ColumnWidth = 8
    Range("E1").Select
    Columns("I:I").EntireColumn.AutoFit
    Columns("J:J").Select
    Selection.ColumnWidth = 8
    Columns("K:K").Select
    Selection.ColumnWidth = 25
    Columns("L:L").Select
    Selection.ColumnWidth = 8
    Range("A1").Select
    Columns("M:AA").Select
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
     Selection.CurrentRegion.AutoFilter Field:=9, Criteria1:= _
        "Master Agreement"
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    ActiveSheet.ShowAllData
  
   
    Columns("D:D").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Columns("D:D").Select
    Selection.ColumnWidth = 8
     Range("A2").Select
    ActiveCell.CurrentRegion.Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
        Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
       
    Range("A2:L2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
Range("L2").Select
    Selection.ClearContents
        ActiveCell.CurrentRegion.Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
        Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    Range("A2").Select
    Columns("J:J").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("K:K").Select
    Selection.Copy
    Columns("J:J").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Notes"
    Columns("J:J").Select
    Selection.ColumnWidth = 15.86
    Columns("D:D").EntireColumn.AutoFit
    Range("A1").Select
 
Last edited by a moderator:
Upvote 0
At quick glance, this looks like a recorded macro with lots of redundant and/or superfluous actions. You might want to look through it for activity (other than autofit) that causes entire rows and/or columns to be formatted. That's not necessary, and as I said in Post #2 it can cause file bloat.

You could also improve your chances of getting some help by using code tags and indentation to reduce the labor any prospective helper must produce in reading the code.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
How to find what is making file size so large?
Replies
3
Views
159
c.clavin
C
F
  • Question
Using macro to save formating ans diminishing file size
Replies
0
Views
158
Fibo
F
U
  • Question
Excel Ribbon button icon change effect
Replies
3
Views
635
jkpieterse
jkpieterse
J
  • Question
Excel workbook file size ballooned - how to compress or make it smaller?
Replies
3
Views
578
Joe4
Joe4
Q
  • Question
Macros help on multiple files to be created from a set of large data
Replies
0
Views
357
questforexcel
Q

Forum statistics

Threads
1,215,829
Messages
6,127,130
Members
449,361
Latest member
VBquery757

Share this page

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
Back
Top