Borders - non-contiguous ranges

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

Hello,

The following macro places borders around the range
A6:D50:

Sub myborders()
Dim ws As Worksheet
Dim Lastrow As Integer

Set ws = Sheets(1)
Lastrow = 50

With ws.Range("A6:D" & Lastrow)
With .Borders
.LineStyle = xlBorderLineStyleContinuous
.Weight = xlThin
End With
End With

End Sub

However, the project has several non-contiguous ranges
that require formatting with borders. I wish to achieve
this without repeating for each range, the code starting
with "With ws.range("A6:D" & Lastrow)".

This is the type of thing that I am looking for (this
syntax does not work):
With ws.range("A6:D" & Lastrow, "K6:L" & Lastrow, "P6:S"
& LastRow)

I will appreciate any suggestions on the above matter.

TIA

Richard
 
Thank Tom, works perfectly. I tried something like that
but obviously had the apostrophes in the wrong place.
I've just replace about 50 lines of code with 6.

Regards,

Richard
 
Tom,

If i wnanted to just put outside boarders (and leave
inside as it is)for non-contiguous ranges , what code
would I need? Thanks.
 
Jamal,

Using Tom's code snippet, this will place a border around
A6:D50, K6:L50, P6:S50. Change to suit. See the VBA Help
file for the BorderAround method, in particular, the
properties to change the line style, line weight and
color.

Sub myBordersAround()
Dim lastrow As Integer
Dim ws As Worksheet
Dim rng As Range

lastrow = 50
Set ws = Sheets(1)
Set rng = ws.Range("A6:D" & lastrow & ",K6:L" & lastrow
& ",P6:S" & lastrow)

' Clear existing borders
' Cells.Borders.LineStyle = xlLineStyleNone

're-establish borders
'rng.BorderAround , , ColorIndex:=xlColorIndexAutomatic
rng.BorderAround LineStyle:=xlContinuous,
Weight:=xlThick, _
ColorIndex:=xlColorIndexAutomatic

End Sub

Regards,
Richard
-----Original Message-----
Tom,

If i wnanted to just put outside boarders (and leave
inside as it is)for non-contiguous ranges , what code
would I need? Thanks.
Click to expand...
 
Richard,
This is very helpful. It changed lot of lines od codes to
a few. Thanks.
-----Original Message-----
Jamal,

Using Tom's code snippet, this will place a border around
A6:D50, K6:L50, P6:S50. Change to suit. See the VBA Help
file for the BorderAround method, in particular, the
properties to change the line style, line weight and
color.

Sub myBordersAround()
Dim lastrow As Integer
Dim ws As Worksheet
Dim rng As Range

lastrow = 50
Set ws = Sheets(1)
Set rng = ws.Range("A6:D" & lastrow & ",K6:L" & lastrow
& ",P6:S" & lastrow)

' Clear existing borders
' Cells.Borders.LineStyle = xlLineStyleNone

're-establish borders
'rng.BorderAround , , ColorIndex:=xlColorIndexAutomatic
rng.BorderAround LineStyle:=xlContinuous,
Weight:=xlThick, _
ColorIndex:=xlColorIndexAutomatic

End Sub

Regards,
Richard
Click to expand...
 
Back
Top