condensing a boxing script

[rhino4eva]

I use


Range("A7", "N" & lastrow).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With

End Sub




to box in a length variable sheet . I used macro recording to get the code but noticed lots of repetition in the code

is there a way of cycling thru the different "borders" controls and condensing this script

 

[wideboydixon]

I assume the setting up of lastrow is done somewhere above. Here's a compressed version of the rest:

Dim borderIndex As Long

With Range("A7", "N" & lastRow)
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    For borderIndex = xlEdgeTop To xlInsideHorizontal
        With .Borders(borderIndex)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
    Next borderIndex
End With

End Sub

WBD

 

[Fluff]

Another option (assuming you have no borders already)

With Range("A7", "N" & lastrow)
    .Borders.LineStyle = xlContinuous
    .Borders.Weight = xlMedium
End With

 

[rhino4eva]

thanks fluff ... life is much more simple now

 

[Fluff]

Glad we could help & thanks for the feedback