Borders in VBA

SonicBoomGolf

Active Member
Joined
Aug 7, 2004
Messages
325
Simple question for the pros here. Is there a way to shorted the code needed to put borders around cells? Attached is some code I recorded with the macro recorder which puts borders around cells.

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


I am trying to use something similar to the following to shorten the code.

Dim formatRng As Range
Set formatRng = Application.Union(xlEdgeLeft, xlEdgeRight, xlEdgeTop, xlEdgeBottom)
With formatRng.Borders
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With


Any ideas??
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
Hi trackman69,

Unfortunately, the Borders collection does not accept an Array argument, as many other collections do. However, you can shorten the code considerably by looping through the borders:

Dim Border As Integer
Dim MyRange As Range

Set MyRange = [B4:Q10] 'range of cells to apply border properties to

For Border = xlEdgeLeft To xlEdgeBottom
With MyRange.Borders(Border)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Next Border


Damon
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
You could just use this:
Code:
With Selection.Borders
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
End With
 

Watch MrExcel Video

Forum statistics

Threads
1,122,561
Messages
5,596,853
Members
414,107
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top