CODEWITHSUNDEEP
vbaexcel
GCC
GCC

Reputation: 295

How to add borders to multiple ranges in VBA?

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

Upvotes: 0

Views: 1984

Answers (1)

SJR
SJR

Reputation: 23081

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

Upvotes: 1

Related Questions