Macro - Selective Row Printing

[jessica_m]

Hello Everyone:

I have an order form that I need a macro for, that allows me to print only certain things on the worksheet. I'm attaching an example below. I need A1:G12 to ALWAYS print. And I need A15:G23 (according to the example) to only print when G has a value of > 0. Can someone attach a macro that I can use for this? That would be great!

 

[JLGWhiz]

It would be preferable to use he actual full sheet name, i.e. Sheets("order form"), where ActiveSheet is used, in case the code runs when the form is not the active sheet. You can do away with the title and closing lines to insert this into other code. It would be inserted just before the "PrintOut" command.

Sub prtut()
Dim lr As Long
lr = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row
If WorksheetFunction.Sum(Range("G15:G" & lr) = 0 Then
 ActiveSheet.PageSetup.PrintArea = "$A$1:$G$12"
Else
 ActiveSheet,PageSetup.PrintArea = "$A$1:$G$" & lr
End If
End Sub

 

[jessica_m]

It would be preferable to use he actual full sheet name, i.e. Sheets("order form"), where ActiveSheet is used, in case the code runs when the form is not the active sheet. You can do away with the title and closing lines to insert this into other code. It would be inserted just before the "PrintOut" command.

Sub prtut()
Dim lr As Long
lr = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row
If WorksheetFunction.Sum(Range("G15:G" & lr) = 0 Then
 ActiveSheet.PageSetup.PrintArea = "$A$1:$G$12"
Else
 ActiveSheet,PageSetup.PrintArea = "$A$1:$G$" & lr
End If
End Sub

JLGWhiz,

Thank you for your response! I tried opening up a Macro by and just copy pasted your formula (subbing the cells correctly) and its coming up with an error. Where am I pasting this?? Am I doing it correctly? I attached a picture of where I'm putting it. Any help would be greatly appreciated. Thank you.

 

[s hal]

instead of orderform, you need to say Sheets("Orderform")

And the else condition should have Sheets("Orderform"). <--- note the period instead of the comma (that was a typo in the other post)

 

[JLGWhiz]

ADVERTISEMENT

JLGWhiz,

Thank you for your response! I tried opening up a Macro by and just copy pasted your formula (subbing the cells correctly) and its coming up with an error. Where am I pasting this?? Am I doing it correctly? I attached a picture of where I'm putting it. Any help would be greatly appreciated. Thank you.

A couple of typos can spoil anything. Here is a revised version tested to avoid the error messages.
This one also includes the print command. I had assumed before that you already had some code that you just wanted to add this bit to. But according to your second post, you intend to run it solo, hence the added print command. If you don't need it, delete it. As for where you put it, I would put it in the standard code module1, where I put all of my code except for event code, which I put in their respective sheets, controls or ThisWorkbook modules. Code in the standard module can be readily accessed from any other module, whereas accessing code in sheets, controls and ThisWorkbook modules from another module gets really tricky.

Sub prtut2()
Dim lr As Long
lr = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row
If WorksheetFunction.Sum(Range("G15:G" & lr)) = 0 Then
 ActiveSheet.PageSetup.PrintArea = "$A$1:$G$12"
Else
 ActiveSheet.PageSetup.PrintArea = "$A$1:$G$" & lr
End If
ActiveSheet.PrintOut
End Sub

 

[jessica_m]

Thank you JLG !

This actually half worked. If the their was 0 values in G, nothing printed, but if I entered ONE value in any category the whole thing printed anyway. Anyway to fix this? Thank you for your time and effort

My best,

 

[JLGWhiz]

Thank you JLG !

This actually half worked. If the their was 0 values in G, nothing printed, but if I entered ONE value in any category the whole thing printed anyway. Anyway to fix this? Thank you for your time and effort

My best,

I created a test setup with one set of characters in rows 1 through 12, columns A through G. I used a different set of characters in rows 13 and 14 with columns the same and a third set of characters in several rows beginning at row 15 and Columns A Through F, leaving Column G15 downward blank for the initial test. The code produced a print area of $A$1:$G$12 which contained the first set of characters only. I then inserted an integer into cell G15 and ran the procedure. It produced a print area of $A$1:$G$15 containing All of the first two character sets and one line of the third character set as well as the integer I had entered. I then entered several integers into column G starting at G15 skipping some cells and then entering more integers and then re-ran the procedure. It produced a print area that included all character sets down to the row containing the last entry in column G. If you copied the code I posted to your standard code module, I do not know why you are not getting similar results.

 

[jessica_m]

JLG,

I think the problem is that I do not need all of the blanks "in between" the first and last integer to print. I only need the rows with values in G! If they do not have a value, they do not need to print.

I created a test setup with one set of characters in rows 1 through 12, columns A through G. I used a different set of characters in rows 13 and 14 with columns the same and a third set of characters in several rows beginning at row 15 and Columns A Through F, leaving Column G15 downward blank for the initial test. The code produced a print area of $A$1:$G$12 which contained the first set of characters only. I then inserted an integer into cell G15 and ran the procedure. It produced a print area of $A$1:$G$15 containing All of the first two character sets and one line of the third character set as well as the integer I had entered. I then entered several integers into column G starting at G15 skipping some cells and then entering more integers and then re-ran the procedure. It produced a print area that included all character sets down to the row containing the last entry in column G. If you copied the code I posted to your standard code module, I do not know why you are not getting similar results.