Combine Ranges to Draw Borders Issue

[Ryan H]

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

 

[Tim Williams]

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

 

[AB]

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

 

[Jacob Skaria]

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