How to add borders to multiple ranges in VBA?

Question

I have been trying to add borders to two ranges on two different worksheets. In my case, sheet one has 14 rows whereas the second worksheet has 30 rows. Each worksheet has the same amount of columns. When I run my code, the first worksheet works fine but the second worksheet only has 14 rows that are bordered and the other 16 are left without a border. Why isn't my code bordering the last 16 columns of my second worksheet?

Sub lines()

Dim wb As Worksheet
Dim wb2 As Worksheet
Dim arrBorders As Variant, vBorder As Variant


Set wb = Worksheets("wb Summary")
Set wb2 = Worksheets("wb2 Summary")
arrBorders = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, _
                       xlEdgeRight, xlInsideVertical, xlInsideHorizontal)

With wb.Range("A2:H" & Cells(Rows.Count, "H").End(xlUp).Row)
    For Each vBorder In arrBorders
    With .Borders(vBorder)
     .LineStyle = xlContinuous

                .Weight = xlThin
            End With
        Next
    End With

With wb2.Range("A2:H" & Cells(Rows.Count, "H").End(xlUp).Row)
    For Each vBorder In arrBorders
    With .Borders(vBorder)
     .LineStyle = xlContinuous
                .Weight = xlThin
            End With
        Next
    End With


End Sub

To add, if I run my code while the second worksheet is open, the opposite occurs. So if wb2 is open (has 30 rows), all 30 rows will be bordered in both wb2 and wb1 — GCC, Jul 05 '17 at 18:50
Welcome to SO! Here's a recommendation: instead of adding additional info as a comment, try editing the original question. — ktdrv, Jul 05 '17 at 19:16
related: [Is the . in .Range necessary when defined by .Cells?](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells) — , Jul 05 '17 at 19:21

SJR · Accepted Answer · 2017-07-05T19:09:10.423

1

You need to fully reference the sheets. I think you can also shorten your code by avoiding the loops.

Sub lines()

Dim wb As Worksheet
Dim wb2 As Worksheet

Set wb = Worksheets("wb Summary")
Set wb2 = Worksheets("wb2 Summary")

With wb.Range("A2:H" & wb.Cells(wb.Rows.Count, "H").End(xlUp).Row)
    .Borders.LineStyle = xlContinuous
    .Borders.Weight = xlThin
End With

With wb2.Range("A2:H" & wb2.Cells(wb2.Rows.Count, "H").End(xlUp).Row)
    .Borders.LineStyle = xlContinuous
    .Borders.Weight = xlThin
End With

End Sub

edited Jul 05 '17 at 19:09

answered Jul 05 '17 at 18:55

SJR

22,986
6
18
26

Thanks so much, that works perfectly. Still new to VBA and I have encountered this problem a lot. This – GCC Jul 05 '17 at 18:57
My pleasure. I would say sheet referencing is probably the single biggest cause of problems. – SJR Jul 05 '17 at 18:58

How to add borders to multiple ranges in VBA?

1 Answers1