asked on

Use VBA to add borders to cells

I have a long list of data and I'd like to put an outside border around every 7 cells going down. How can I do this (preferably in VBA since I'm trying to learn it but a formula is also welcome)

Martin Liss

I added code around a recorded macro. It assume you want to start in A1.

Sub Macro1()

Dim lngLastRow As Long
Dim lngrow As Long

lngLastRow = ActiveSheet.UsedRange.Rows.Count
Range("A1").Select

For lngrow = 1 To lngLastRow Step 7

    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.Offset(7, 0).Select
Next
End Sub

Open in new window

Tj a

ASKER

Thank you. Instead of just bordering every 7th cell, I want to border every 7 cells so I can have blocks of 7. See the attached file for an example.
borders.xlsx

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

I edited the above so that it starts at row 2 and won't stop at blank rows in the data.

Tj a

ASKER

That's perfect. Thank you.

Martin Liss

You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014