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