Run time error 6

AlexMach

New Member
Joined
Apr 6, 2009
Messages
17
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?

Code:
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​
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,229
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

New Member
Joined
Apr 6, 2009
Messages
17
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

New Member
Joined
Apr 6, 2009
Messages
17
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

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,229
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,105
Members
416,161
Latest member
David1966Lewis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top