Help With Recorded Macro

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,407
Office Version
  1. 2016
Platform
  1. Windows
I have recorded a macro below that performs some formatting. I want to use it on various files but the problem I have it does the whole sheet down to a million rows and takes some time to complete. What needs adding please so it only does the amount of rows that actually have data in to speed it up please?

Code:
Sub Formatting()
'
' Formatting_ Macro
'

'
    Cells.Select
    With Selection.Font
        .Name = "Arial"
        .Size = 10
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection.Font
        .Name = "Arial"
        .Size = 10
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    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
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Cells.EntireColumn.AutoFit
    Range("A1").Select
End Sub
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,425
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Try changing
VBA Code:
Cells.Select
to
VBA Code:
ActiveSheet.UsedRange.Select
 

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,407
Office Version
  1. 2016
Platform
  1. Windows
That's better, thanks.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,425
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You're welcome, you should also be able to use the code below (untested)

VBA Code:
Sub Formatting()
    '
    ' Formatting_ Macro
    '

    '
    With ActiveSheet.UsedRange
        
        With .Font
            .Name = "Arial"
            .Size = 10
            .Underline = xlUnderlineStyleNone
            .TintAndShade = 0
            .ThemeFont = xlThemeFontNone
        End With
        
        With .Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        
        With .Font
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
        End With
        
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders.LineStyle = xlNone
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        
    End With
    Cells.EntireColumn.AutoFit
    Range("A1").Select
End Sub
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,055
Office Version
  1. 2016
Platform
  1. Windows
EDIT : Oops ! Didn't see post #4

You might want to tidy up the code :
VBA Code:
Sub Formatting()
With ActiveSheet.UsedRange
    With .Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With .Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    .Borders.LineStyle = xlNone
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    .EntireColumn.AutoFit
End With
Range("A1").Select
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,113,938
Messages
5,545,108
Members
410,656
Latest member
Hydraulics
Top