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:

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
614
Office Version
  1. 2013
Platform
  1. Windows
Just replace your code named
"FormatThis(Target As Range)"
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
614
Office Version
  1. 2013
Platform
  1. Windows
<< I need this macro to put in my files as part of a 'before close' >>
In workbook code
VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
FormatThis Range("A1:B100")
End Sub

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

Watch MrExcel Video

Forum statistics

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