VBA basics
35. Format Cells (ruled line, border)

Excel macro basics and applications, introduction to Excel VBA
last updated:09-01-2019

35. Format Cells (ruled line, border)

Some of this topic was processed by machine translation. Original


Sometimes it is necessary to draw a ruled line with macro VBA.
If the data range of the sheet is changed by a macro, it will be necessary to redraw the ruled lines.


When searching the net, etc., the macro that draws ruled lines seems to be written in various ways.
This is the worst case of using the VBA code created using macro recording.

Macro recording when grid lines are drawn in A1 to B5 cells after 2010



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

It's a long macro.
But this is
The following macro will do it.

Sub Macro2()
Range("A1:B5").Borders.LineStyle = xlContinuous
End Sub

If you deal with macros somewhat,
At the very least, don't just use the previous macro created in the macro recording.
The following explains the ruled lines in macro VBA.

Ruled line specification in macro VBA

he border of the cell (Range object) becomes the Borders collection.
The Borders collection is a collection of Border objects.
(More on the collection later)
Handle cell borders with Range.Borders.
That means
This means that the Range's Borders property handles the Border objects in the Borders collection.

Range.Borders. Property = setting value
Or
Range.Borders(index).property = setting value
Specify the following for Index:

xlDiagonalDown A border from the upper left corner to the lower right of each cell in the range
xlDiagonalUp A border from the lower left corner to the upper right corner of each cell in the range
xlEdgeBottom Lower border in range
xlEdgeLeft The leftmost rule in the range
xlEdgeRight The right border in the range
xlEdgeTop Upper border in range
xlInsideHorizontal Horizontal borders for all cells in the range except for the borders outside the range
xlInsideVertical Vertical borders for all cells in the range except for the borders outside the range

Omit parentheses ()
Range.Borders. Property
If you do this,
xlEdgeBottom, xlEdgeLeft, xlEdgeRight, xlEdgeTop, xlInsideHorizontal, xlInsideVertical
All of these have the same settings.

Borders properties



Property Property Description Property Setting Value Setting Value Description
LineStyle Types of ruled lines xlNone or xlLineStyleNone None
xlContinuous solid line
xlDash Broken line
xlDashDot 1-dot chain line
xlDashDotDot 2-dot chain line
xlDot dotted line
xlDouble 2 main lines
xlLineStyleNone No line
xlSlantDashDot Diagonal line
Color Ruled line color Long integer representing RGB value
ColorIndex Ruled line color 1~56 Index of 56 color palettes up to 2003
It is better to use Color because it is difficult to understand what color it is
Weight Ruled line thickness xlHairline Fine line (thinner ruled line)
xlMedium usually
xlThick Thick line (thickest ruled line)
xlThin Extra fine
TintAndShade Ruled line color lighter or darker 2007 or later

Specified value
If only one of LineStyle or Weight is specified, the ruled line is drawn with the default value.
LineStyle is xlContinuous
Weight is xlThin
In other words, if only xlContinuous is specified, Weight will be xlThin.

There are some combinations of property settings.
This is because only combinations that can be specified in "Format Cells" on the worksheet are possible.

VBA macro borders

Macro VBA can only be set to those that can be specified here.
Naturally,
Macros cannot be written unless you fully understand the basic operations of Excel.

Example of use
Range("Range of cells").Borders(xlEdgeLeft).LineStyle = xlContinuous
Range("Range of cells").Borders(xlEdgeRight).LineStyle = xlContinuous
Range("Range of cells").Borders(xlEdgeLeft).Weight = xlThick
Range("Range of cells").Borders(xlEdgeRight).Weight = xlThick

In the above, heavy practice is drawn to the left and right of the cell range.

VBA macro borders

Range("Range of cells").Borders.LineStyle = xlContinuous

In the above, a rectangular thin line is drawn in the cell range.

VBA macro borders

Range.BorderAround method

There is another way to handle ruled lines.
That is the Range.BorderAround method.
This draws a ruled line at a time against the outer frame.

Range.BorderAround LineStyle, Weight, {ColorIndex | Color | ThemeColor}

Only one of {ColorIndex | Color | ThemeColor} can be specified.

The specification method is the same as the previous property,

This is a method (giving the object an action)

LineStyle, Weight, ColorIndex, Color, ThemeColor

These are the arguments.

It is specified with a named argument.

Example of use
Range("Range of cells").BorderAround LineStyle:=xlContinuous, Weight:=xlMedium, Color:=vbRed

Above,
A solid line, a normal thickness, and a red ruled line are drawn on the outer frame of the cell range.

VBA macro borders

Precautions for ruled lines in macro VBA

When drawing a ruled line with a macro, the number of lines in the VBA code varies greatly depending on how it is written.
If there are a lot of lines in the VBA code, the resulting macro will be very slow and hard to read. .
Please devise to draw an efficient and concise ruled line.
To do that, draw a ruled line together on the same ruled cell range.
For example, when you want to draw a ruled line like this,

VBA macro borders

Range("Range of cells").Borders.LineStyle = xlContinuous
Range("Range of cells").BorderAround Weight:=xlThick

You can draw ruled lines in this way.
The ruled line of the outer frame will be drawn twice,
Rather than drawing ruled lines one by one, the description of macro VBA is short and concise.



Articles with the same theme "VBA basics"

32. Format Cells (Alignment)
33. Format Cells (Font)
34. Format Cells (fill, interior)
35. Format Cells (ruled line, border)
36. General practice question 4
37. Specifying a book sheet
38. Set formula in cells
39. Clear the cells
40. Copy, cut and paste of cells
41. Copy cells and paste values (PasteSpecial)
42. What is copying a cells?



  • >
  • >
  • 35. Format Cells (ruled line, border)

  • If you find this site useful, please share and bookmark.


    I'm going to pay close attention to the description,
    If you have any mistakes or suggestions,「Contact Us」I hope you will let me know.
    Use the posted VBA code at your own risk.
    We are not liable for any damages such as data corruption.




    If you find this site useful, please share and bookmark.
    To the bottom of the text