Print Rows only if greater than zero

[Tom Woodford]

I would like to make a worksheet for a material requisition but only print the line on the worksheet if the quantity required is greater than zero

The blank worksheet would contain columns as follows

Qty Required : item stock # : description : Weight : Cost : Extended Weight : Extended Cost :


so this worksheet might contain 2000 items but the requisition would probably only ask for a limited number (maybe 10-12)

So the goal is to only print the line if the Qty Requred is greater than zero

Can anyone help with that Macro.

 

[Michael M]

Hi Tom and welcome to the forum
Ypu don't need a macro to do this.
Simply filter the column by non blanks and then print the visible cells

 

[Tom Woodford]

ok, but I would like the requisition to print with a header section including name and job number filled in by the user
the center would be the requested material (items greater than zero)
and the bottom would be totaled values and the totaled weights

hopefully so they fill out the job name, number, enter the qty of each item they want an then get a clean printout
of he requisition with a single button push

 

[Michael M]

Hi Tom....Had to play golf yesterday, so your question had to wait !
try this, but whre I have made the "adjust to suit" comment it basically is allowing 3 rows for your title block.
If your title block is bigger, change the 3 to however many rows your title block covers

Sub MM1()
Dim lr As Long, r As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "A").End(xlUp).Row
    For r = lr To 3 Step -1 'adjust this line to suit
        If Range("A" & r).Value = "" Then Rows(r).Hidden = True
    Next r
    With ActiveSheet
        .PageSetup.PrintArea = "=" & ActiveSheet.UsedRange.Address
        .PrintOut Copies:=1, Collate:=True
    End With
Cells.Rows.Hidden = False
Application.ScreenUpdating = True
End Sub

 

[Tom Woodford]

Michael,

Thanks for working on this for me honestly I barely have an idea how that code works but I did a mockup and used the macro you wrote but found that it wanted to print all the lines after the last row that had a quantity entered in it. So it was excluding lines without quantities required but then after the last quantity required was encountered it would print all the remaining rows whether they had quantities required or not and it was trying to print a huge quantity of blank pages too

I know I'm adding complexity as I go here but ideally it would stop printing as soon as the qty reqd column was not populated.

Can you show me how t make it stop printing. I tried using a print range to limit the macro but it seemed to ignore it and wanted to print zillions of blank pages

**Fact is I will need the sheet to print the column total too but if the rest of this could work then I'm thinking all I have to do is put any value into that column at the end row where the totals are and then that would print to.


Thank you for your help
Tom

 

[Michael M]

OK, do a CTRL + End and see where the cursor ends up !!
If it's a long way down and over I'd suggest you need to "clean" you worksheet.
To do this
Select the row 1 row past the last use row and press CTRL + SHIFT + Down Arrow
Right click a row header and press delete
Now select a column 1 past the last column used and press CTRL + SHIFT Right Arrow
Right click a column header and press delete
Save and close
when you reopen it should only want to print the used range

 

[Tom Woodford]

Nice!
That worked except that it still printed any remaining blank rows that followed the last row with a value, however that does not bother me
because I will want it to sum all the columns at the bottom anyhow so if I have anything in there it prints the row for me then stops at the bottom of the sheet

Again Thanks a lot I appreciate your help, with any luck I can struggle through from here, maybe,, will see if I come back later
Thanks
Tom

 

[Michael M]

Okay, glad you got it sorted...