creating macro to print all columns but only rows with data

[jkarns]

Hello all,


I've created a template in excel to calculate various results (mean, standard deviation, etc.) I have column A dedicated to numbering (1-100); B-O for users to enter their own information, columns P-W have various calculation formulas. Rows 12-111 are where data (analyte names and associated values for calculations) will be entered. My co-workers will use this, so I want this to automatically print the appropriate selection without them having to do a print selection. They may have anywhere from 1-100 analytes to calculate. The Sheet name is MDL Calc Form.

I always want all columns to print and I always want the first 11 rows to print, but I only want the rows from 12:111 to print if data is entered in merged column B:C (Analyte Name). Unfortunately for me, since I have formulas extending down to 111, the macro I was trying to use reads that as a value and I cannot figure out a way around this.

So, always print area: A1-A11:W1-W11
Only print A12-W12 if BC12 has value
Only print A13-W13 if BC13 has value, etc. etc.

Also, I think I can save the workbook to automatically print 'fit all columns on one page' so it always prints that way, but in case I am wrong please include that also. I couldn't figure out a way to attach the excel file either.

I would be forever grateful to anyone who could write me a macro to do this. I am utterly lost and have little coding knowledge. Please help!

Thank you!!
jkarns

[Keebellah]

To attach a file: Just press 'Go Advanced' button below and choose Manage attachments, it's simple, follow the instructions.

[jkarns]

[attach]xlsm.gif DRAFT L40.20 r0 MDL Calculation Form with Summary.xlsm[/attach]

Let's try this maybe? Well that's one problem down! Thanks Keebellah!

[Keebellah]

Okay, got the file, yo should at least take into account that a Division by 0 is not allowed. A sheet full of #DIV/0! that's 'not-done'

[Keebellah]

Okay, try this one
There is a small Printer Icon on the MDL form sheet

[jkarns]

So it still printed all the rows. Is it possible to make the macro read if a name is typed into column BC, row 12,13,14 etc. it will only print that row? So in other words, if there are only two analytes entered, I only want it to print those two rows (plus the first 11 rows.) Any suggestions?

[Keebellah]

If cell B is empty that row will NOT be printed.
I tested it and it works, so you'll have to check what you've done.
Did you copy my code to another file? Then you forgot the code in the Sheet.

I though you wanted to print the top 11 rows in the table, all you have to do is modify the macro:

Public Function Prepare_MDL_Calc_Form()
'
' PrintMDL_Calc_Form Macro
'
If ActiveSheet.Name <> "MDL Calc Form" Then Exit Function
Application.ScreenUpdating = False
Dim ws      As Worksheet
Dim xrow    As Long
Dim lstRow  As Long
Set ws = Worksheets("MDL Calc Form")
lstRow = 111
Application.Calculation = xlCalculationManual
For xrow = lstRow To 23 Step -1
    If Len(Trim(ws.Cells(xrow, "B").Value)) = 0 Then ws.Cells(xrow, "B").EntireRow.Hidden = True
Next xrow
Application.Calculation = xlCalculationAutomatic

Change 23 to 12 which is the first row of your table

[Keebellah]

Apart from that I think you should do it as follows:

Check this code and add the line in BOLD RED

Public Sub Print_MDL_Calc_Form()
If MsgBox("Print MDL SUMMARY FORM?", vbQuestion + vbYesNo, "") <> vbYes Then Exit Sub
Worksheets("MDL Calc Form").Select
Prepare_MDL_Calc_Form
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
    IgnorePrintAreas:=False
resetHiddenRows
End Sub

You can remove the code in Thiswokbook

Private Sub Workbbok_BeforePrint etc

End Sub

[jkarns]

Awesome! It worked on hiding all the cells! The problem is, I don't want to run the macro until the data has been entered and ready to print. So type in all data, save as (whatever data this corresponds to), then when whoever goes to print, I want the macro to run. I had to copy the macro into the original file I had. I attached a copy of it in the thread so I don't have the printer icon. Everything else works, but the macro starts as soon as the file is opened. Can this be avoided?

[Keebellah]

Open both files, then just select the printer icon from the file I uploaded, got to your file and on that page select where you want it and select Paste.
Then all you have to do is reassign the macro to the one in your working file.
The rows will be hidden only when the print command is given (the macro) and then the rows will become visible again.

[jkarns]

My problem has been solved. Thank you Keebellah for all of your help. I am very grateful!!

[jkarns]

I'm getting an error Worksheets ("MDL Calc Form").Select line

I added the Prepare_MDL_Calc_Form

[jkarns]

I get Run-Time error '9': Subscript out of range when I hit the print button. It worked on the original sheet, just not when I copied it.

[Keebellah]

You're welcome.

[jkarns]

Keebellah, I am having issues again. Once I copied the macro it was linking it to the workbook from here (which now for some reason it can't find.) Could you copy and paste the macro into a thread so I can create a new one and copy it from here? The excel file is linked and I feel like all of this is starting to melt my brain...

Please help.

[Keebellah]

The red macro is the one that should be linked to the print command or icon

Option Explicit

Public Sub Print_MDL_Calc_Form()
If MsgBox("Print MDL SUMMARY FORM?", vbQuestion + vbYesNo, "") <> vbYes Then Exit Sub
Worksheets("MDL Calc Form").Select
Prepare_MDL_Calc_Form
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
    IgnorePrintAreas:=False
resetHiddenRows
End Sub

Public Function resetHiddenRows()
Application.ScreenUpdating = False
Dim ws      As Worksheet
Dim xrow    As Long
Dim lstRow  As Long
Set ws = Worksheets("MDL Calc Form")
ws.Range(Cells(12, "A"), Cells(100, "A")).EntireRow.Hidden = False
Application.ScreenUpdating = True
End Function

Public Function Prepare_MDL_Calc_Form()
'
' PrintMDL_Calc_Form Macro
'
If ActiveSheet.Name <> "MDL Calc Form" Then Exit Function
Application.ScreenUpdating = False
Dim ws      As Worksheet
Dim xrow    As Long
Dim lstRow  As Long
Set ws = Worksheets("MDL Calc Form")
lstRow = 111
Application.Calculation = xlCalculationManual
For xrow = lstRow To 12 Step -1
    If Len(Trim(ws.Cells(xrow, "B").Value)) = 0 Then ws.Cells(xrow, "B").EntireRow.Hidden = True
Next xrow
Application.Calculation = xlCalculationAutomatic

    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = "$A$1:$W$111"
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = "FOR MRLLC USE ONLY"
        .CenterFooter = ""
        .RightFooter = _
        "Mean Recovery %: Red font indicates failure, acceptable range 70-130%" & Chr(10) & "Column A: MDL > 1/10 concentration of spike, * indicates failure" & Chr(10) & "Column B: MDL < spike concentrataion, * indicates failure" & Chr(10) & "&P"
        .LeftMargin = Application.InchesToPoints(0.236220472440945)
        .RightMargin = Application.InchesToPoints(0.236220472440945)
        .TopMargin = Application.InchesToPoints(0.748031496062992)
        .BottomMargin = Application.InchesToPoints(0.94488188976378)
        .HeaderMargin = Application.InchesToPoints(0.31496062992126)
        .FooterMargin = Application.InchesToPoints(0.31496062992126)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
Application.ScreenUpdating = True
End Function

[jkarns]

I think I finally got it working. Thank you for all of your help! I truly appreciate it!