Dynamic Range Borders – VBA code assistance

[bootj1234]

Following is some VBA code I am wrestling with in trying to create borders that stay within in a dynamic range.

As you can see from the following VBA code and image, I am able to get the code I have written to be dynamic in responding to finding the overall range of the data I want to enclose in borders, except for the thicker vertical borders. These thicker vertical borders are extending below the dynamic range, with row 19, as you can see in the image, constituting the last row in the range in this particular instance.

I would appreciate assistance with making the necessary adjustments in my code to get the thicker vertical borders to work within the dynamic range like the thinner borders do.

Thanks for your assistance!

-John

Sub DynamicRangeBorders()
Dim StartCell As Range
Set StartCell = Range("A11")
Cells.Borders.LineStyle = xlNone
StartCell.CurrentRegion.Borders.LineStyle = xlContinuous
Dim BorderIndex As Variant
For Each BorderIndex In Array(xlEdgeTop, xlEdgeLeft, xlEdgeRight)
With Range("L11:R35").Borders(BorderIndex)
.Weight = xlThick
.ColorIndex = vbBlack
End With
Next BorderIndex
For Each BorderIndex In Array(xlEdgeLeft)
With Range("R11:R35").Borders(BorderIndex)
.Weight = xlThick
.ColorIndex = vbBlack
End With
Next BorderIndex
End Sub

[/IMG]

 

[DanteAmor]

Try this

Sub DynamicRangeBorders()
    Dim StartCell As Range
    Dim BorderIndex As Variant
[COLOR=#0000ff]    Dim lr As Long[/COLOR]
    
    Set StartCell = Range("A11")
    Cells.Borders.LineStyle = xlNone
    StartCell.CurrentRegion.Borders.LineStyle = xlContinuous
[COLOR=#0000ff]    lr = Range("A" & Rows.Count).End(xlUp).Row[/COLOR]
    For Each BorderIndex In Array(xlEdgeTop, xlEdgeLeft, xlEdgeRight)
        With Range([COLOR=#0000ff]"L11:R" & lr[/COLOR]).Borders(BorderIndex)
            .Weight = xlThick
            .ColorIndex = vbBlack
        End With
    Next BorderIndex
    For Each BorderIndex In Array(xlEdgeLeft)
        With Range([COLOR=#0000ff]"R11:R" & lr[/COLOR]).Borders(BorderIndex)
            .Weight = xlThick
            .ColorIndex = vbBlack
        End With
    Next BorderIndex
End Sub

 

[Fluff]

How about

Sub DynamicRangeBorders()
   Dim StartCell As Range
   Set StartCell = Range("A11")
   Cells.Borders.LineStyle = xlNone
   StartCell.CurrentRegion.Borders.LineStyle = xlContinuous
   Dim BorderIndex As Variant
   For Each BorderIndex In Array(xlEdgeTop, xlEdgeLeft, xlEdgeRight)
      With Range("L11", Range("R" & Rows.Count).End(xlUp)).Borders(BorderIndex)
         .Weight = xlThick
         .ColorIndex = vbBlack
      End With
   Next BorderIndex
   For Each BorderIndex In Array(xlEdgeLeft)
      With Range("R11", Range("R" & Rows.Count).End(xlUp)).Borders(BorderIndex)
         .Weight = xlThick
         .ColorIndex = vbBlack
      End With
   Next BorderIndex
End Sub

 

[DanteAmor]

You could also eliminate the loop in this way:

Sub DynamicRangeBorders()
    Dim StartCell As Range
    Dim lr As Long
    
    Set StartCell = Range("A11")
    Cells.Borders.LineStyle = xlNone
    StartCell.CurrentRegion.Borders.LineStyle = xlContinuous
    lr = Range("A" & Rows.Count).End(xlUp).Row
    With Range("L11:L" & lr & ",R11:R" & lr & ",S11:S" & lr)
        .Borders(xlEdgeLeft).Weight = xlThick
        .Borders(xlEdgeLeft).ColorIndex = vbBlack
    End With
    With Range("L11:R11")
        .Borders(xlEdgeTop).Weight = xlThick
        .Borders(xlEdgeTop).ColorIndex = vbBlack
    End With
End Sub

 

[bootj1234]

Hi Fluff,

Thanks for your quick reply to my post yesterday. Both yours and Dante's solution did the trick.
As a person who learns best by doing, I always try to solve things myself before reaching the point where it’s time to learn from others. Fortunately, when the time to learn rolls around, I continue to receive great support from Mr. Excel Message Board members like you and Dante!

Kindest regards,

-John

 

[bootj1234]

Hi Dante,

Thanks for your quick reply to my post yesterday. Both yours and Fluff's solution did the trick.
As a person who learns best by doing, I always try to solve things myself before reaching the point where it’s time to learn from others. Fortunately, when the time to learn rolls around, I continue to receive great support from Mr. Excel Message Board members like you and Fluff!

Kindest regards,

-John

 

[Fluff]

You're welcome & thanks for the feedback

 

[DanteAmor]

I'm glad to help you. I appreciate your kind comments.