MS Excel VBA: Border Around Each Printed Page

Question

I got the VBA code below to add borders around the printed pages of an excel (MS Excel 2013) worksheet but I got error codes as follows: "Runtime Error '1004': Application-defined or object-defined error". The debug highlighted the lines in block quotes. How do I resolve this please?

Sub Create_Borders_Around_Pages()
    Dim rngBorder As Range
    Dim lngLastRow As Long
    Dim lngLastCol As Long
    Dim lngHPBreak As Long
    Dim lngVPBreak As Long
    Dim lngRow As Long
    Dim lngCol As Long
    Dim rngAC As Range

    With ActiveSheet
        Set rngAC = ActiveCell
        lngLastRow = .UsedRange.Cells(.UsedRange.Rows.Count, 1).Row
        lngLastCol = .UsedRange.Cells(1, .UsedRange.Columns.Count).Offset(1, 0).Column
        .Cells(lngLastRow + 1, 1).Activate

        lngRow = 1
        For lngVPBreak = 1 To .VPageBreaks.Count
            lngCol = 1
            For lngHPBreak = 1 To .HPageBreaks.Count
                Set rngBorder = .Range(.Cells(lngRow, lngCol), _
                .Cells(.HPageBreaks(lngHPBreak).Location.Row - 1, .VPageBreaks(lngVPBreak).Location.Column - 1))
                rngBorder.BorderAround xlContinuous, xlThick
                lngRow = .HPageBreaks(lngHPBreak).Location.Row
            Next

            Set rngBorder = .Range(.Cells(lngRow, lngCol), .Cells(lngLastRow, .VPageBreaks(lngVPBreak).Location.Column - 1))
            rngBorder.BorderAround xlContinuous, xlThick

            lngCol = .VPageBreaks(lngVPBreak).Location.Column
        Next
        lngRow = 1
        For lngHPBreak = 1 To .HPageBreaks.Count

Set rngBorder = .Range(.Cells(lngRow, lngCol), _ .Cells(.HPageBreaks(lngHPBreak).Location.Row - 1, lngLastCol))

            rngBorder.BorderAround xlContinuous, xlThick
            lngRow = .HPageBreaks(lngHPBreak).Location.Row
        Next
        Set rngBorder = .Range(.Cells(lngRow, lngCol), .Cells(lngLastRow, lngLastCol))
        rngBorder.BorderAround xlContinuous, xlThick
        rngAC.Activate
    End With
End Sub

Answer 1

Code works fine unless there are no VPageBreaks in your sheet.

The variable lngCol is only being set within the For lngVPBreak = 1 To .VPageBreaks.Count loop, so if there are no vPageBreaks it doesn't get set.

As a result your Set rngBorder line is failing with a column value of 0 in the .Cells(lngRow, lngCol) part of the statement.

Suggest you set lngCol = 1 outside of this loop somewhere?