2
\$\begingroup\$

Is there a better way to format the cells with borders than what you get when you record a macro? For example, I want to add borders to a cell range. The recorded code is:

Range("A1:C19").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ColorIndex = 0
    .TintAndShade = 0
    .Weight = xlThin
End With

Can this be shortened?

\$\endgroup\$

migrated from stackoverflow.com Mar 1 '17 at 20:30

This question came from our site for professional and enthusiast programmers.

  • \$\begingroup\$ Yes, it can be shortened but it depends on which border attributes you want to change. More information on how you want the borders to be formatted will be helpful. \$\endgroup\$ – J. Garth Mar 1 '17 at 17:34
  • \$\begingroup\$ Like in the code, the top, bottom, right and left. \$\endgroup\$ – Selrac Mar 1 '17 at 17:38
3
\$\begingroup\$

If the question is "how do I apply the same formatting to multiple borders?" then the following is one way:

Dim the_borders As Variant
the_borders = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlInsideVertical, xlInsideHorizontal)
    ' Or whatever xlEdge* constants you want to list

Dim idx As Long
For idx = LBound(the_borders) To UBound(the_borders)
    With Selection.Borders(the_borders(idx))     ' Process the right border
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
Next idx

The As Variant / Array combo gives you an array of the border IDs you specify, running from LBound(the_borders) to UBound(the_borders). Then, within the loop, borders(idx) is the XlBordersIndex you can pass to Selection.Borders.

\$\endgroup\$
2
\$\begingroup\$

You can also use. I added the selection range so you get intellisense.

Dim selectionRange As Range
Set selectionRange = Selection

Dim theLineStyle As XlLineStyle
theLineStyle = xlContinuous

With selectionRange
    .BorderAround theLineStyle 
    .Borders(xlInsideHorizontal).LineStyle = theLineStyle
    .Borders(xlInsideVertical).LineStyle = theLineStyle
end With
\$\endgroup\$
  • \$\begingroup\$ Not bad, although I would recommend using Selection.Range instead of selectionRange --- you will still have IntelliSense and you won't need an extra variable :) . \$\endgroup\$ – cxw Mar 1 '17 at 21:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.