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

[squeakums]

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.

 

[JoeMo]

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.

 

[squeakums]

Here is the entire macro, if you know of a way I can edit this let me know, thanks:

    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

 

[JoeMo]

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.