Selecting and Formatting Borders in VBA

Question

I'm trying to add thick borders on the left and right side every five columns, going all the way to the last row of the table (there are no blanks in any rows).

My code only adds the borders on the first and second rows.

I use following line twice:

Range(Selection, Selection.End(xlDown)).Select

Here's what a portion of the spreadsheet looks like. Note that the first row is a merged cell, and in the second row are table headers.

Application.DisplayAlerts = False
Dim lastCol As Integer
lastCol = ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column

For i = 2 To lastCol Step 5

    Range(Cells(1, i), Cells(1, i + 4)).Select

    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With

    'Add thick borders
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select 'here's where I'm struggling
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Next i

Application.DisplayAlerts = True

Read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba and all your problems may disappear (VBA-related ones at least). — SJR, Jan 07 '20 at 16:11
Also, is this a list object? (defined table). If so, you can step directly through the columns index until the columns count and format those blocks that way ... also without using select as @SJR mentioned above. — Scott Holtzman, Jan 07 '20 at 16:13
Thank you SJR. I've heard that .select is not ideal, but honestly didn't know other ways to accomplish what I'm doing. Thanks for the link. — Sudio, Jan 07 '20 at 17:02

Scott Holtzman · Accepted Answer · 2020-01-07T17:41:56.553

1

The picture appears to be a list object (structured table in Excel). If so, you can work directly with the table object in VBA and avoid a ton of code writing and logic building.

Dim t as ListObject
Set t = ws.ListObjects("myTable")

Dim i as Long
For i = 1 to t.ListColumns.Count Step 5

    With t.ListColumns(i).Range.Resize(t.ListRows.Count,5).Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With

    'same for right border

Next

edited Jan 07 '20 at 17:41

answered Jan 07 '20 at 16:18

Scott Holtzman

27,099
5
37
72

I tried the above, but get "object doesn't support this property or method"...I believe because the first row is not part of the table (table begins in row 2). – Sudio Jan 07 '20 at 16:59
1

@Sudio see my edit. i forgot to specify the `Range` object of the `ListColumn`. If you only want data to be border, use `DataBodyRange` instead. – Scott Holtzman Jan 07 '20 at 17:25
Gotcha. It errors on the first line of the 'with' statement. I did change the listobject name to match my table, and ws is set to the correct sheet name. – Sudio Jan 07 '20 at 17:29
@Sudio - see my edited comment, I realized my mistake – Scott Holtzman Jan 07 '20 at 17:29
Thanks @Scott. I did try again, but run in to the error 'object doesn't support this property or method' on the line 'With t.ListColumns...'. If version of Excel matters, I'm on Office 365. – Sudio Jan 07 '20 at 17:36
Should t.rows.count be t.ListRows.Count? – Sudio Jan 07 '20 at 17:44
1

@Sudio - yes, that was my edit. or `t.Range.Rows.Count` works, too. – Scott Holtzman Jan 07 '20 at 17:45

Selecting and Formatting Borders in VBA

1 Answers1