Select multiple ranges using cells property

[Spike]

I will be very grateful for the code to select two ranges at the same time.
I am formatting these ranges with borders etc and rather than do each one in
turn would like to do them at the same time. The two ranges are as below.

Range(Cells(5, 4), Cells(intRows, 5))

Range(Cells(5, 6), Cells(intRows, 7))

Everything I have tried selects the whole area and not separately, using D5
etc is simple but as this is dynamic I need to use the Cells property

many thanks

 

[OssieMac]

Hello Spike,

2 Options. You can select the ranges by using Union and then use the
Selection to set the borders like the following.

Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code.

Option 1.

Sub Macro1()
Dim intRows As Long

intRows = 10 'Assigned for testing

Sheets("Sheet1").Select
Union(Range(Cells(5, 4), _
Cells(intRows, 5)), _
Range(Cells(5, 6), _
Cells(intRows, 7))).Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub
'End of first Option
'**************************************

2nd Option. No need to select the ranges. Just assign a Union of the ranges
to a variable and use the range variable. (More professional.)

Sub Macro2()
Dim rngBorders As Range
Dim intRows As Long

intRows = 10 'Assigned for testing

With Sheets("Sheet1")
Set rngBorders _
= Union(.Range(.Cells(5, 4), _
.Cells(intRows, 5)), _
.Range(.Cells(5, 6), _
.Cells(intRows, 7)))
End With

rngBorders.Borders(xlDiagonalDown).LineStyle = xlNone
rngBorders.Borders(xlDiagonalUp).LineStyle = xlNone
With rngBorders.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With rngBorders.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With rngBorders.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With rngBorders.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
rngBorders.Borders(xlInsideVertical).LineStyle = xlNone
rngBorders.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub

 

[Spike]

Thank you. I have already tried Union and that does not keep the two ranges
separate, so i get one border around the whole lot. I wish to have a border
around each individual range.

So in effect the only difference is a line between the two ranges, i have
further ranges not adjacent which i wish to select at the same time but did
not mention this to keep it simple.

 

[Jacob Skaria]

Try the below...


intRows = 10

Range(Range(Cells(5, 4), Cells(intRows, 5)).Address & "," & _
Range(Cells(5, 6), Cells(intRows, 7)).Address).BorderAround Weight:=xlThin

 

[Spike]

spot on. Many thanks much appreciated

 

[Jacob Skaria]

When you have more ranges; to be selected dynamically ..you should be able to
have a loop to build the address string..separated by comma and then use that
with Range() as below

strAddress = "D5:E10,F5:G10,..,..,..,.."
Range(strAddress).Select

 

[Spike]

how do i select two individual cells using the cells propertly to format in
the same way.
ie Range (Cells(5, 4) and
Range(Cells(5, 5)

so that i get a border line around each one

 

[Jacob Skaria]

Try

Range(Cells(5, 4).Address & "," & Cells(5, 5).Address).BorderAround _
Weight:=xlThin

 

[Spike]

thanks. That was the one combination i had not tried!