Is there a better or more efficiant way of writing this short bit of VBA code?

Question

The VBA Code below, which is part of a much larger sub that pulls and formats data from various other sheets, is formatting the first row, Range A1:I1 which contains headers.

It appears to work as expected but is there a simpler or more efficiant way of doing exactly what this code is doing?

Do I really need to include things such as:

.Strikethrough = False
.Superscript = False
.TintAndShade = 0
.ThemeFont = xlThemeFontNone

and so on or is it best practice to include them, I would rather do things the correct way.

    Rows("1:1").RowHeight = 32
    Range("A1:I1").Select
With Selection.Font
    .Name = "Arial"
    .Size = 11
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
End With
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
With Selection.Font
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
End With
Range("A1").Select

End Sub

please see: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba for some guidance on how to avoid using `.Select`, which just slows down the code. and yes you do NOT need the fluff that the recorder puts in in most cases, this being one of them. there are some commands that the seeming extraneous items are in fact needed. — Scott Craner, Apr 16 '21 at 15:04

score 0 · Answer 1 · answered Apr 16 '21 at 15:24

Formatting Cells

Here's what I think is important in your particular case. But some of it may depend on the previous formatting.
It's best to test on your own. Remove some rows that seem to contain default values. Study those various properties.

Option Explicit

Sub testFormatting()
    With Rows(1)
        .RowHeight = 32
        With .Columns("A:I")
            With .Font
                .Name = "Arial"
                .Size = 11
                .ThemeColor = xlThemeColorDark1
            End With
            .Interior.ThemeColor = xlThemeColorLight1
        End With
    End With
End Sub

score 0 · Answer 2 · answered Apr 16 '21 at 15:26

As far as doing things the correct way, never use .Select but instead use direct referencing

Dim r as Range
Set r = Range("A1").Resize(1,9)  ' Reference to A1:I1
r.EntireRow.RowHeight = 32
With r.Font
    .Name = "Arial"
    ' ...
End With

and so on

score 0 · Answer 3 · answered Apr 16 '21 at 18:30

It is convenient to make your own style (programmatically or manually) and apply to cells:

Sub createHeaderStyle() ' or create the style by UI
    Dim st As Style
    Set st = ThisWorkbook.Styles.Add("headerStyle")
    With st
        .Font.Bold = True
        '... and so on
    End With
End Sub

' application
Sub applyStyle(rng As Range)
    rng.Style = "headerStyle"
End Sub

Subsequently, you can interactively edit the style properties

Is there a better or more efficiant way of writing this short bit of VBA code?

3 Answers3

Formatting Cells