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

S

squeakums

Well-known Member
Joined
May 15, 2007
Messages
839
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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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

SandsB
  • Question Question
Move from shared drive to Sharepoint - killed my macro
Replies
7
Views
161
Alex Blakenburg
A
C
  • Question Question
How to find what is making file size so large?
Replies
3
Views
243
c.clavin
C
C
  • Solved
Extracting data with VBA macro button from files in designated folder
2 3
Replies
20
Views
859
Micron
M
amadams
  • Question Question
Macro to AutoSave Excel Spreadsheets Using Different File Names
Replies
1
Views
448
NdNoviceHlp
NdNoviceHlp
T
  • Question Question
Automatically Copying Data To A New Workbook When Closing A Workbook
Replies
4
Views
428
Tmini
T

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

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