none
VB - remove bottom border on odd rows between A2:B366? RRS feed

  • Question

  • Lifesavers that you all are, I spend too much time manually selecting odd-numbered rows from A2 to B366 to remove the bottom border (after making a change to the borders, which I need to do periodically, changing one way and then back).

    There must be a way to remove the border on the bottom of odd numbered rows, no (i.e., 1, 3, 5, 7, 9, 11, 13 ... 361, 363, 365)?  How can this be done, please?  I'm sure it must be easy for an expert but I sure haven't gotten far on several googles searches <sigh>.  I get the usual colouring of odd-numbered rows, etc., despite putting vb in query ...

    Thank you!!

    Cheers!

    Monday, February 9, 2015 9:44 PM

Answers

  • Here is a macro you can use:

    Sub RemoveBorders()
        Dim r As Long
        Application.ScreenUpdating = False
        For r = 1 To 365 Step 2
            Range("A" & r).Resize(ColumnSize:=2) _
                .Borders(xlEdgeBottom).LineStyle = xlLineStyleNone
        Next r
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by SGFan Monday, February 16, 2015 5:25 PM
    Monday, February 9, 2015 9:55 PM

All replies

  • Here is a macro you can use:

    Sub RemoveBorders()
        Dim r As Long
        Application.ScreenUpdating = False
        For r = 1 To 365 Step 2
            Range("A" & r).Resize(ColumnSize:=2) _
                .Borders(xlEdgeBottom).LineStyle = xlLineStyleNone
        Next r
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by SGFan Monday, February 16, 2015 5:25 PM
    Monday, February 9, 2015 9:55 PM
  • Here is a macro you can use:

    Sub RemoveBorders()
        Dim r As Long
        Application.ScreenUpdating = False
        For r = 1 To 365 Step 2
            Range("A" & r).Resize(ColumnSize:=2) _
                .Borders(xlEdgeBottom).LineStyle = xlLineStyleNone
        Next r
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thank you, Hans!

    This has been working beautifully. I really played around with the file to finetune it so the only thing that I found was that I had added extra rows somewhere along the way so that I had 378 rows. When those rows didn't have the border removed underneath I just increased the number above to 400. It should be 365 because this is a calendar spreadsheet but having a few more days listed doesn't go any harm at all. So the sheet is working perfectly with the adjusted number as I've left those extra rows in.

    Thanks once again and cheers!

    Monday, February 16, 2015 5:27 PM