Combine Ranges to Draw Borders Issue
 

I have a Sub that I want to combine some ranges and draws borders around
them. The ranges are actually next to each other and when I use the Union
function it combines the ranges all into one address instead of a bunch of
addresses.

For example: the below code shows rng.Address = $C$10:$H$14, thus borders
get drawn around that range. But I want to draw borders around all the
little ranges I specified in rng, thus I want
rng.Address = $C$10:$E$14,$F$10:$F$14,$G$10:$G$14,$H$10:$H$14

Is there a way to do this?

Sub DrawBorders()

Dim rng As Range

' union ranges that need vertical lines
Set rng = Union(.Range("C" & lngFirstRow & ":E" & lngLastRow), _
.Range("F" & lngFirstRow & ":F" & lngLastRow), _
.Range("G" & lngFirstRow & ":G" & lngLastRow), _
.Range("H" & lngFirstRow & ":H" & lngLastRow))
Debug.Print rng.Address

' adds thin vertical lines to separate columns
With rng
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
End With

End Sub
--
Cheers,
Ryan

Combine Ranges to Draw Borders Issue
 

sub DoAllBorders()
DrawBorders .Range("C" & lngFirstRow & ":E" & lngLastRow)
DrawBorders .Range("F" & lngFirstRow & ":F" & lngLastRow)
DrawBorders .Range("G" & lngFirstRow & ":G" & lngLastRow)
DrawBorders .Range("H" & lngFirstRow & ":H" & lngLastRow)
end sub

Sub DrawBorders(rng as range)
' adds thin vertical lines to separate columns
With rng
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
End With
End Sub



Tim


"Ryan H" wrote in message
...
I have a Sub that I want to combine some ranges and draws borders around
them. The ranges are actually next to each other and when I use the Union
function it combines the ranges all into one address instead of a bunch of
addresses.

For example: the below code shows rng.Address = $C$10:$H$14, thus borders
get drawn around that range. But I want to draw borders around all the
little ranges I specified in rng, thus I want
rng.Address = $C$10:$E$14,$F$10:$F$14,$G$10:$G$14,$H$10:$H$14

Is there a way to do this?

Sub DrawBorders()

Dim rng As Range

' union ranges that need vertical lines
Set rng = Union(.Range("C" & lngFirstRow & ":E" & lngLastRow), _
.Range("F" & lngFirstRow & ":F" & lngLastRow), _
.Range("G" & lngFirstRow & ":G" & lngLastRow), _
.Range("H" & lngFirstRow & ":H" & lngLastRow))
Debug.Print rng.Address

' adds thin vertical lines to separate columns
With rng
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
End With

End Sub
--
Cheers,
Ryan

Combine Ranges to Draw Borders Issue
 

Maybe something along these lines:

Sub BorderMyRanges()
Dim RangesToHaveBorder As Collection
Dim oneRange As Range

'Instanceate the Collection object
Set RangesToHaveBorder = New Collection

'Add range object to the collection
RangesToHaveBorder.Add Range("A1", "A10") 'Adjust to your
according range
RangesToHaveBorder.Add Range("B1", "B10") 'Adjust to your
according range
RangesToHaveBorder.Add Range("C1", "C10") 'Adjust to your
according range
RangesToHaveBorder.Add Range("D1", "D10") 'Adjust to your
according range

'loop through the collecction and apply borders to each range
separatelly
For Each oneRange In RangesToHaveBorder
With oneRange
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
End With
Next oneRange

End Sub


On Nov 27, 4:40*pm, Ryan H wrote:
I have a Sub that I want to combine some ranges and draws borders around
them. *The ranges are actually next to each other and when I use the Union
function it combines the ranges all into one address instead of a bunch of
addresses. *

For example: *the below code shows rng.Address = $C$10:$H$14, thus borders
get drawn around that range. *But I want to draw borders around all the
little ranges I specified in rng, thus I want
rng.Address = $C$10:$E$14,$F$10:$F$14,$G$10:$G$14,$H$10:$H$14

Is there a way to do this?

Sub DrawBorders()

Dim rng As Range

* * * * ' union ranges that need vertical lines
* * * * Set rng = Union(.Range("C" & lngFirstRow & ":E" & lngLastRow), _
* * * * * * * * * * * * .Range("F" & lngFirstRow & ":F" & lngLastRow), _
* * * * * * * * * * * * .Range("G" & lngFirstRow & ":G" & lngLastRow), _
* * * * * * * * * * * * .Range("H" & lngFirstRow & ":H" & lngLastRow))
* * * * Debug.Print rng.Address

* * * * ' adds thin vertical lines to separate columns
* * * * With rng
* * * * * * .Borders(xlEdgeLeft).Weight = xlThin
* * * * * * .Borders(xlEdgeRight).Weight = xlThin
* * * * * * .Borders(xlEdgeTop).Weight = xlThin
* * * * * * .Borders(xlEdgeBottom).Weight = xlThin
* * * * End With

End Sub
--
Cheers,
Ryan

Combine Ranges to Draw Borders Issue
 

Once you combine using Union since the address is $C$10:$H$14 the borders
will be just around that range. Try

Set rng = Range("$C$10:$E$14,$F$10:$F$14,$G$10:$G$14,$H$10:$ H$14")
rng.BorderAround Weight:=xlThin

If this post helps click Yes
---------------
Jacob Skaria


"Ryan H" wrote:

I have a Sub that I want to combine some ranges and draws borders around
them. The ranges are actually next to each other and when I use the Union
function it combines the ranges all into one address instead of a bunch of
addresses.

For example: the below code shows rng.Address = $C$10:$H$14, thus borders
get drawn around that range. But I want to draw borders around all the
little ranges I specified in rng, thus I want
rng.Address = $C$10:$E$14,$F$10:$F$14,$G$10:$G$14,$H$10:$H$14

Is there a way to do this?

Sub DrawBorders()

Dim rng As Range

' union ranges that need vertical lines
Set rng = Union(.Range("C" & lngFirstRow & ":E" & lngLastRow), _
.Range("F" & lngFirstRow & ":F" & lngLastRow), _
.Range("G" & lngFirstRow & ":G" & lngLastRow), _
.Range("H" & lngFirstRow & ":H" & lngLastRow))
Debug.Print rng.Address

' adds thin vertical lines to separate columns
With rng
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
End With

End Sub
--
Cheers,
Ryan