0

I found code which find all PageBreak on sheet (automatic PB after PageSetup) and I could not figure out how to explain this behavior. When I run code, PB is 2 rows away from PB, but when debugging (no matter where I put breakpoints, it works as expected.

My sheets have 100 to few thousand rows and firstly I format it as I need, then need insert PB before every row which have No.2 in column A. Then, I need put border to every page break (does not matter if is automatic or manual).

Code is:

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

lngCol = 1
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

(I commented VPageBreaks because I do not need them)

So, this code is making border 2 rows after PB, then 4 row after on second page, then 6 rows away, and so on....

But... when I set breakpoint in VBA... it work just fine putting borders exactly where needed.

How to explain that???? How to debug such a code?????

Thanks in advance

Tom

Tommy301
  • 21
  • 4
  • Here, read on [how to avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) looks like you are not working on the sheet you need. Do you select the sheet when you are debugging? – Damian Jun 06 '19 at 13:45
  • yes,.... whole code is executed on same sheet in both case... with and without debugging... – Tommy301 Jun 06 '19 at 13:48

0 Answers0