Creating Borders Loop

[tsxlee]

Hello,

I'm looking to create a loop where row ranges are put into borders. i.e. Range B1:B24 has borders then two rows are left blank then B27:B50 has borders. This then loops.

I've recorded an excel macro but I can't figure out how to make it loop.
Any help would be appreciated. Here's what I have:

range("B1:B24").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub

 

[Simon Lloyd]

Try this:

Sub Borders()
Dim Rng As Range, i As Long
For i = 1 To 200 Step 26
With Range("B" & i & ":B" & i + 23)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
Next i
End Sub

Change the large value in For i = 1 to 200 Step 26 for the nuber of rows to process.

 

[AlphaFrog]

This builds off of Simon Lloyd's code and just adds a 2nd loop for the borders part.

Sub Borders()
    Dim Rng As Range, i As Long, [COLOR="Blue"]bdr As Integer[/COLOR]
    For i = 1 To 200 Step 26
        With Range("B" & i & ":B" & i + 23)
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
            .Borders(xlInsideHorizontal).LineStyle = xlNone
[COLOR="Blue"]            For bdr = 7 To 10
                With .Borders(bdr)
                    .LineStyle = xlContinuous
                    .Weight = xlThin
                    .ColorIndex = xlAutomatic
                End With
            Next bdr[/COLOR]
        End With
    Next i
End Sub

 

[Simon Lloyd]

I did nothing really, just made it work, didn't feel up to tidying the code up . it's 01:10am here

 

[tsxlee]

Thanks a lot. Works great!