none
Excel 2016 vba macro to set borders to a cell range with filtered cells inside gives error (with Excel 2013 everything is fine) RRS feed

  • Question

  • I have made a vba macro that works fine with Excel 2013, but have error with Excel 2016. The macro is very simple and is taken from "recorded macro": it sets borders to some cells.

    The problem (I suppose) is that cells included also filtered rows.

    Step 1: create a sheet with the following cells

    column_1 
    cells(1;1) = "aa"
    cells(2;1) = 2
    cells(3;1) = 1
    cells(4;1) = 2
    cells(5;1) = 1
    cells(6;1) = 1

    Step 2:  filtered with "1" on the first row

    Step 3: running the following macro, you have error '1004' on ".weight " row

                 and the pressing "Debug" you have the error line underlined


    Step 4: Now, if you stop the macro now and try to save the file, you'll get the following  error


    Please note that this happens only with Excel 2016, Excel 2013 has no problems

    This is the complete macro: Option Explicit

            Sub test()
                Sheets(1).Select
                Range("A1:A6").Select
                Selection.Borders(xlDiagonalDown).LineStyle = xlNone
                Selection.Borders(xlDiagonalUp).LineStyle = xlNone
                With Selection.Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlThin   ' ==>>>>ERROR HERE
                End With
                With Selection.Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With Selection.Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With Selection.Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With Selection.Borders(xlInsideVertical)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlThin
                End With

    Andrea

    Thursday, April 11, 2019 6:52 AM

All replies

  • Andrea De,
    Re:  borders in a filtered list

    Have you tried applying the borders then filtering the list?

    '---
    Custom_Functions add-in (19 of them)
    Download from MediaFire...

    Thursday, April 11, 2019 11:36 AM
  • Hi, it works. But I don't understand why it doesn't work on Excel 2016 and it works on Excel 2013.

    Please note that applying borders to a filtered list it applies borders only to filtered items (This is my goal), while running the way you suggest I need to add more codes :-)

    Andrea

    Thursday, April 11, 2019 11:50 AM
  • Andrea De,
    re:  "I don't understand why it doesn't work on Excel 2016"

    Each new XL release is "improved".
    The list of improvements for xl2013 is found here...
    https://support.office.com/en-us/article/Discontinued-features-and-changed-functionality-in-Excel-2013-C417E377-26C6-4F43-BE6D-16718DD75FFA

    I can't find a similar list for xl2016.
    I don't have xl2016 and can't verify your experience;  you may have an improvement.
    Thursday, April 11, 2019 12:33 PM