Shorten Code

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,407
Office Version
  1. 2016
Platform
  1. Windows
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.

Code:
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
 
Last edited:

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
L

Legacy 456155

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

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,407
Office Version
  1. 2016
Platform
  1. Windows
Don't understand.
 
L

Legacy 456155

Guest
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

Well-known Member
Joined
Jan 24, 2011
Messages
3,407
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
 
L

Legacy 456155

Guest
Perhaps...
VBA Code:
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

Well-known Member
Joined
Jan 24, 2011
Messages
3,407
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Legacy 456155

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

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
614
Office Version
  1. 2013
Platform
  1. Windows
Or Even,
VBA Code:
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

Well-known Member
Joined
Jan 24, 2011
Messages
3,407
Office Version
  1. 2016
Platform
  1. Windows
Or Even,
VBA Code:
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,113,936
Messages
5,545,105
Members
410,656
Latest member
Hydraulics
Top