Borders - non-contiguous ranges

[Richard]

Hello,

The following macro places borders around the range
A650:

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

Set ws = Sheets(1)
Lastrow = 50

With ws.Range("A6" & 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" & Lastrow)".

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

I will appreciate any suggestions on the above matter.

TIA

Richard

 

[Tom Ogilvy]

With ws.range("A6" & Lastrow & ",K6:L" & Lastrow & ",P6:S"
& LastRow)

 

[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

 

[Jamal]

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.

 

[Richard]

Jamal,

Using Tom's code snippet, this will place a border around
A650, 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" & 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.

 

[Jamal]

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
A650, 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" & 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