Borders - non-contiguous ranges
 

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

Borders - non-contiguous ranges
 

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

--
Regards,
Tom Ogilvy



"Richard" wrote in message
...
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

Borders - non-contiguous ranges
 

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

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

--
Regards,
Tom Ogilvy



"Richard" wrote in
message
...
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



.

Borders - non-contiguous ranges
 

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.

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

--
Regards,
Tom Ogilvy



"Richard" wrote in
message
...
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



.

Borders - non-contiguous ranges
 

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.

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

--
Regards,
Tom Ogilvy



"Richard" wrote
in
message
...
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



.

.

Borders - non-contiguous ranges
 

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

-----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.

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

--
Regards,
Tom Ogilvy



"Richard" wrote
in
message
.. .
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



.

.

.