Automated formatting ends prematurely

Question

I set up a macro to format rows in a spreadsheet if there are entries. I set it up using a file with not many entries and when I use it on a file with more entries its stops early.

I've looked at the code and it makes no sense to me why it won't function properly.

Range(Range("A2"), Range("E2").End(xlDown)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .Weight = xlThin
End With

Hi, first thing you should do is not use select. Here's a great resource for it: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Badja, May 17 '19 at 12:01
What you mean by `stops early`... do you get an error? It doesn't do what you are expecting? Also, please be more explicit about `file with more entries`... does this mean a bigger range than the one in the macro? — FAB, May 17 '19 at 12:17
Your code only puts a bottom border on the last used row. Is that what you are trying to do? The reason it stops early is; using `End(xlDown)`, will cause your code to stop if you have a blank cell in column E before the last used cell. If you want it to put the bottom border on the last used row, the you need to use `End(xlUp)`, Also, Use `With` and remove `Select` — GMalc, May 17 '19 at 12:42
Thanks everybody for your help. It seems to be working correctly now. — Thomas Ward, May 17 '19 at 14:28

score 0 · Answer 1 · answered May 17 '19 at 12:06

Here's an example, untested:

Sub BordersAndFilters()


    Dim ws As Worksheet
    Dim aBorderSettings() As Long           'Calls the array
    Dim i As Long

    Set ws = ThisWorkbook.Worksheets("Data") 'your worksheet name

    ReDim aBorderSettings(1 To 8, 1 To 2)   'An Array of length 8x2 (table)
        aBorderSettings(1, 1) = xlDiagonalDown:     aBorderSettings(1, 2) = xlNone
        aBorderSettings(2, 1) = xlDiagonalUp:       aBorderSettings(2, 2) = xlNone
        aBorderSettings(3, 1) = xlEdgeBottom:       aBorderSettings(3, 2) = xlContinuous
        aBorderSettings(4, 1) = xlEdgeLeft:         aBorderSettings(4, 2) = xlNone
        aBorderSettings(5, 1) = xlEdgeRight:        aBorderSettings(5, 2) = xlNone
        aBorderSettings(6, 1) = xlEdgeTop:          aBorderSettings(6, 2) = xlNone
        aBorderSettings(7, 1) = xlInsideHorizontal: aBorderSettings(7, 2) = xlNone
        aBorderSettings(8, 1) = xlInsideVertical:   aBorderSettings(8, 2) = xlNone

    With ws.Range("A2:E" & ws.Cells(ws.Rows.Count, "E").End(xlUp).Row)  'Instead of using LastRow. The "E" should be your column with guaranteed values in every row.
        'Filter and Fit
        .AutoFilter 'remove this if you don't want to filter
        .EntireColumn.AutoFit 'remove this if you don't want to fit the cells to column width

        'For every number in the array, change the borders based on the values in the array
        For i = LBound(aBorderSettings, 1) To UBound(aBorderSettings, 1)
            .Borders(aBorderSettings(i, 1)).LineStyle = aBorderSettings(i, 2)
            If aBorderSettings(i, 2) <> xlNone Then
                .Borders(aBorderSettings(i, 1)).ColorIndex = xlAutomatic
                .Borders(aBorderSettings(i, 1)).TintAndShade = 0
                .Borders(aBorderSettings(i, 1)).Weight = xlThin
            End If
        Next i

    End With

 End Sub

score 0 · Answer 2 · answered May 17 '19 at 12:54

Per my comments, the easiest way to fix your code is to modify it like this;

With Sheets("Sheet1").Range("A2:E" & Cells(Rows.Count, "A").End(xlUp).Row)
    'Identify the worksheet, use xlUp, and remove Select/Selection
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    .Borders(xlEdgeLeft).LineStyle = xlNone
    .Borders(xlEdgeTop).LineStyle = xlNone
    With .Borders(xlEdgeBottom) 
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
End With

Automated formatting ends prematurely

2 Answers2