Shorten Code

[Dazzawm]

I have recorded the following macro to do some formatting. Do I need all of it and can it be reduced, meaning has the recording added unnecessary code ? Also can the same code be used on different files with different amount of rows and columns? Thanks.

Sub Macro1()
'
' Macro1 Macro
'

'
    With Selection.Font
        .Name = "Arial"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection.Font
        .Name = "Arial"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    With Selection
        .HorizontalAlignment = xlGeneral
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlLeft
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Cells.Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A1").Select
End Sub

 

[Legacy 456155]

Perhaps you might create a style and then use that by applying it to your ranges.

 

[Dazzawm]

Don't understand.

 

[Legacy 456155]

From your Home tab, select Cell Styles, select New Cell Style. Set the properties/formats and name the style. Now you can apply that style to any range using this:

Range("A1:A10").Style = "MyStyle"

 

[Dazzawm]

I should have added I need this macro to put in my files as part of a 'before close', that's why I asked if it could be reduced and could be used on different sized files.

 

[Legacy 456155]

Perhaps...

Private Sub Workbook_Open()
    FormatThis Range("A1:B100")
End Sub

Sub FormatThis(Target As Range)
    Target.ClearFormats
    
    With Target.Font
        .Name = "Arial"
        .Size = 10
    End With

    Target.BorderAround xlNone
    
    With Target
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .MergeCells = False
        .EntireColumn.AutoFit
    End With

    Range("A1").Select
End Sub

 

[Dazzawm]

Thanks but it appears just to do range A1:B100? I will need it on various files of different amounts of rows and columns?

 

[Legacy 456155]

It will format whichever range you pass in as an argument. It's up to you to provide that.

 

[mohadin]

Or Even,

Sub FormatThis(Target As Range)
    With Target
        .Font.Name = "Arial"
        .Font.Size = 10
        .BorderAround xlNone
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .EntireColumn.AutoFit
    End With
    Range("A1").Select
End Sub

 

[Dazzawm]

Or Even,

Sub FormatThis(Target As Range)
    With Target
        .Font.Name = "Arial"
        .Font.Size = 10
        .BorderAround xlNone
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .EntireColumn.AutoFit
    End With
    Range("A1").Select
End Sub

Thanks

How would I put that in an already before close code, as this is a sub of its own?