Heeelp! Total Excel crash upon printing sheet

[nilsenk]

Hello to you all!
I hope someone can help me with this.

I have made a worksheet for planning activities with many formulas, macros and VBA code, even though I am not an expert (you in this forum have given many good ideas and solutions)

When I have my "activity plan"-sheet active and try to print or print preview Excel crashes badly, recovers my file, but many times with damage. This happens only relating to the print function (and VBA PrintOut method)

Only once I have received the error: Automation error (Error 440), right before excel crashed, when I tried to "debug step into" the PrintOut method, but I cannot find out what the error is and I cannot step into the PrintOut method anyway.

Setting all the printer setup settings works fine (see code below), so the problem must be in printing its self.

This code has been copied by someone else.

Sub myPrinterSetup()
Dim ActPlanSheet As Worksheet
Set ActPlanSheet = Worksheets("Activity Plan")

    With ActPlanSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "Activity Plan - Print Date &D"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(1#)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .PrintTitleRows = "$1:$1"
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape  'xlPortrait
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With

End Sub

Here is the print code that fails:

Sub PrintNow()
    Dim Msg
    
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
                                         Preview:=False, _
                                         ActivePrinter:="\\KBG1PRT3\KBG1-K082-PS", _
                                         PrintToFile:=False, _
                                         Collate:=True ', _
                                         'PrToFileName:="C:\Temp\Dummy.pdf"
    
End Sub

However, the printer manages to print a couple of pages, but some of the fields that are calculated are missing. Additionally, I have freezed the pane in the crossing of column E and row 2, and on the printout of every new page there comes an empty row in between the header and the continuing rows printed.

The strange thing is that i happens only when trying to print my "activity plan"-sheet, all the other sheets printing works fine!!!!! Also, some time ago it worked fine (before 3-4 weeks of programming had passed)

I tried to copy all and paste all into a new worksheet, and delete the old one. This did not help.

How can I find this error? What is the error? How can I get around it?

I "think" I experienced this error for the first time when I tried to set printer area in VBA accedentally to outside the visible area and defined scroll area.

I appreciate it grately if someone can help.

Best Regards,
K

 

[XLGibbs]

Not sure why the crash, but instead of freezing panes, hide the blank row

(highlight the row, right click > hide)

Or in code

Sheets("Activity Plan").Range("A2").EntireRow.Visible = False

would hide Row 2

 

[nilsenk]

Thanks for the reply, but maybe I did not describe it sufficiently good enough. The empty row appears only when printing due to the error, for the pages that are actually printed. And it is not on the first page, always on all the others. It is not in the sheet originally. Also, it seems that excel crashes when reaching print or page 1, because all higher number pages are printed ok, except for the empty row, and page one lacks data (in many of the cells on paper)

Microsoft replied at first, via the auto error notification procedures, that they had corrected many bugs similar to this one and that I needed to upgrade with a service pace/bug fix. I did this, and still this error is equally bad.

Thanks!
K