Run time error 6

[AlexMach]

This macro was created in Excel 2003 and works fine however once the tool containing this macro is converted to an xlsm format with Excel 2010 I get the run time error 6 where indicated.

Would anyone know how to fix this so it runs in Excel 2010 without issues?

Sub OTBPageBreak()

Dim thePB As Integer
Dim lastrow As Long
Dim therow As Long
Dim startrow As Long
Dim endrow As Long

    wsOTB.Activate
    
    ActiveSheet.Unprotect Password:="GRMRS"
    
    wsOTB.PageSetup.PrintArea = "A4:AE370"

    With wsOTB
        .ResetAllPageBreaks
        ActiveWindow.View = xlPageBreakPreview
        '.VPageBreaks(1).DragOff Direction:=xlToRight, regionindex:=1
    End With
    
    thePB = 1
    
    lastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
               
    startrow = 4
    For therow = 4 To lastrow
    
        Cells(therow, 2).Activate
    
        If ActiveCell.EntireRow.Hidden = False Then
        
            If wsOTB.HPageBreaks.Count = thePB Then
                wsOTB.HPageBreaks(thePB).DragOff Direction:=xlDown, regionindex:=1
            Else
                endrow = therow
   ====          If Range("B" & startrow & ":B" & endrow).SpecialCells(xlCellTypeVisible).Count = 47 Then   ***Run Time Error 6***
                    
                    If ActiveSheet.HPageBreaks.Count < thePB Then
                        ActiveSheet.HPageBreaks.Add Range("A" & therow)
                    Else
                        Set ActiveSheet.HPageBreaks(thePB).Location = Range("A" & therow)
                    End If
                    startrow = therow
                    endrow = therow
                    thePB = thePB + 1
                End If
            End If
        End If
    Next

    With wsOTB.PageSetup
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With
    
ActiveSheet.Protect Password:="GRMRS"
    
ActiveWindow.View = xlNormalView
ActiveSheet.DisplayPageBreaks = False

Thanks in advance​

 

[Tom Urtis]

Both systems (2003 and 2010) executed the code line without a problem for me. But maybe your last row is much larger than what I tested, not having your workbook in front of me for perspective, which gave rise to the #6 overflow error. That you are seeing the error in 2010 might be due to all the additional rows on the grid.

Just a suggestion and guess, try altering this
If Range("B" & startrow & ":B" & endrow).SpecialCells(xlCellTypeVisible).Count = 47 Then
to this
If Range("B" & Clng(startrow) & ":B" & Clng(endrow)).SpecialCells(xlCellTypeVisible).Count = 47 Then

 

[AlexMach]

Thanks for your fast reponse Tom. So i tried you suggestion but unfortunately it still came back with the same error.

anything else you can recommend. my lasrow is never greater than 370

 

[AlexMach]

i forgot to mention that for most PCs that work with 2010 it works just 1 or 2 that have this error. As for my PC is on 2003 and I do not get this error either

 

[Firefly2012]

What's the value of startrow and endrow when this error occurs? You should be able to hover your mouse pointer over the variable within the VBE and see what value it holds.

 

[Tom Urtis]

And as another way of looking at this, perhaps you can explain in words, and not just with code that is erroring, what it is you are wanting to do. There might be a better overall approach to totally avoid the mindefiled you're in.