Another multiple range selection question

[erke0034]

Hello everyone,

This is my first post. I couldn't find the right answer I want, that's why I am posting another select multiple range question. I've checked the forum and found couple of methods to select multiple ranges, but none of them worked for me.

here is my question:

This is my original code:

'Format cell Borders


Range("A1:E39,F1:H39,I1:K39,L1:N39,O1:R39,S1:V39").Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
End With​

so the program selects couple of ranges and changes the right and left borders.

i wanted to edit the code so the program will look at the last row and the selection will be A1 to E"lastrow", F1 to H"lastrow" ... etc.

So I used this code: (last row is 60)

'Format cell Borders
lastRow = ActiveSheet.UsedRange.Rows.Count

"'Union(Range("A:E,F:H,I:K,L:N,O:R,S:V"), Range("1:" & lastRow)).Select​

Dim c1, c2, c3, c4, c5, c6, MultipleRange As Range​

Set c1 = Range("A1:E" & lastRow)​

Set c2 = Range("F1:H" & lastRow)​

Set c3 = Range("I1:K" & lastRow)​

Set c4 = Range("L1:N" & lastRow)​

Set c5 = Range("O1:R" & lastRow)​

Set c6 = Range("S1:V" & lastRow)​

Set MultipleRange = Union(c1, c2, c3, c4, c5, c6)​

MultipleRange.Select​

With Selection.Borders(xlEdgeLeft)​

.LineStyle = xlDouble​

.ColorIndex = xlAutomatic​

End With​

With Selection.Borders(xlEdgeRight)​

.LineStyle = xlDouble​

.ColorIndex = xlAutomatic​

End With​

Now program selects A1 to V60 and only changes the left border of A and right border of V

if I change the "union" code as;

Set MultipleRange = Union(c1, c3, c5)

now program selects the first, third and fifth group of ranges and formats right and left borders, since the second and fourth group of ranges also sharing the same borders I got what I need except there is no right border for S1:V60.

I hope this is not too long, but I need your help to resolve this problem. I am open to other codes like;

'Format cell Borders​

lastRow = ActiveSheet.UsedRange.Rows.Count​

Intersect(Range("A:E,F:H,I:K,L:N,O:R,S:V"), Range("1:" & lastRow)).Select​

With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.ColorIndex = xlAutomatic
End With​

but this one also only formats left border of A and right border of V

Thank you,

 

[JLGWhiz]

Something like this maybe.

LastRow = 14
With Range("A1:V" & LastRow)
    .Borders(xlEdgeLeft).LineStyle = xlDouble
    .Borders(xlInsideVertical).LineStyle = xlDouble
    .Borders(xlEdgeRight).LineStyle = xlDouble
End With

 

[erke0034]

Something like this maybe.

LastRow = 14
With Range("A1:V" & LastRow)
    .Borders(xlEdgeLeft).LineStyle = xlDouble
    .Borders(xlInsideVertical).LineStyle = xlDouble
    .Borders(xlEdgeRight).LineStyle = xlDouble
End With

Than you for the reply, but what I need is formatting the borders of;

left border of A,
between E and F,
between H and I,
between K and L
between N and O,
between R and S;
and right border of V

Thank you,

 

[JLGWhiz]

Sub borderline()
Dim c1 As Range, c2 As Range, c3 As Range, c4 As Range, c5 As Range, c6 As Range, ary As Variant, lastRow As Long
lastRow = ActiveSheet.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
Set c1 = Range("A1:E" & lastRow)
Set c2 = Range("F1:H" & lastRow)
Set c3 = Range("I1:K" & lastRow)
Set c4 = Range("L1:N" & lastRow)
Set c5 = Range("O1:R" & lastRow)
Set c6 = Range("S1:V" & lastRow)
ary = Array(c1, c2, c3, c4, c5, c6)
For i = LBound(ary) To UBound(ary)
    With ary(i)
        .Borders(xlEdgeLeft).LineStyle = xlDouble
        .Borders(xlEdgeRight).LineStyle = xlDouble
    End With
Next
End Sub

 

[erke0034]

Sub borderline()
Dim c1 As Range, c2 As Range, c3 As Range, c4 As Range, c5 As Range, c6 As Range, ary As Variant, lastRow As Long
lastRow = ActiveSheet.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
Set c1 = Range("A1:E" & lastRow)
Set c2 = Range("F1:H" & lastRow)
Set c3 = Range("I1:K" & lastRow)
Set c4 = Range("L1:N" & lastRow)
Set c5 = Range("O1:R" & lastRow)
Set c6 = Range("S1:V" & lastRow)
ary = Array(c1, c2, c3, c4, c5, c6)
For i = LBound(ary) To UBound(ary)
    With ary(i)
        .Borders(xlEdgeLeft).LineStyle = xlDouble
        .Borders(xlEdgeRight).LineStyle = xlDouble
    End With
Next
End Sub

Great that works!!

Thank you again

 

[JLGWhiz]

Great that works!!

Thank you again

You're welcome,
Regards, JLG