Combine Ranges to Draw Borders Issue

  • Thread starter Thread starter Ryan H
  • Start date Start date
R

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
 
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
 
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
 
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
 
Back
Top