We help IT Professionals succeed at work.

Excel 2003 - VBA to loop through border elements

TigerMan
TigerMan asked
on
Hi,
Is there a way to code the below so that is it more condensed?

    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

Can this be looped in some way i.e.

For each Border.Type
       .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
Next Border.Type
Comment
Watch Question

Excel VBA Developer
Top Expert 2014
Commented:
You can loop through those values like so.

-Glenn

Sub All_Borders()
    Dim x As Integer
    For x = xlEdgeLeft To xlInsideHorizontal 'values 7 to 12
    With Selection.Borders(x)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Next x
End Sub

Open in new window

Author

Commented:
Nice and thanks.