Print Selectd Rows

[Guest]

I am trying to print out an excel sheet where only rows with column J not
equal to zero print. I used the following code found on a linked website.
When I print, I still get all 500 rows. Any idea why this code does not work?

Thanks

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Quotations")
For rw = 1 To 500
If Application.WorksheetFunction.CountJ( _
.Cells(rw, 1).Range("J1")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("J1").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

 

[Guest]

Have you stepped through the macro to make sure the rows are actually being
hidden? or if you are running 2000/2003 you could do an advanced data sort
"Value <> 0" and then print rows remaing

 

[Tom Ogilvy]

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Quotations")
.rows.Hidden = False
For rw = 2 To 500
If .Cells(rw,"J").Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.rows.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

 

[Guest]

Still didn't work, everything printed and when I went to run macro off the
tools menu, I got a compile error with "If.Cells(rw,"J").Value = 0 Then_"
line hilighted.

Any ides?

 

[Tom Ogilvy]

IN contrast, it worked fine for me, whether I ran it as a stand alone macro
or if I ran it from a menu button. Interestingly, my macro had no line
"If.Cells(rw,"J").Value = 0 Then_"

I had a period in front of Cells in my suggestion.

 

[Tom Ogilvy]

Sorry, looking closer, the period is hidden next to the If in my mail
software (proportional fonts - boo). But another error is you don't have a
space before the underscore and you don't have a space before the period
(then it would have been both syntacically correct and more visible).
"If.Cells(rw,"J").Value = 0 Then_"
"If .Cells(rw,"J").Value = 0 Then _" is correct


Again, copied from my original posting and pasted into a module, it ran with
no problems.

 

[Guest]

Tom,

Thank you. It works perfectly if I go to the Tools menu and run it from
there, but it does not engage when I hit the print button or print from the
file menu. Any ideas? Thanks again, we're a lot closer.

 

[Tom Ogilvy]

You would have to use the beforeprint event in the thisworkbook module if
you wanted it to trigger automatically

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If lcase(Activesheet.Name) = "quotations" then
Hide_Print_Unhide
End if
End Sub


go to the vbe, in the project explorer double click on the thisworkbook
entry of your workbook. in the resulting module, in the left dropdown at
the top select Workbook and in the right, BeforePrint

Make it look like the above.

See Chip Pearson's page on events for an overview of Excel Events if you are
not familiar with them.

http://www.cpearson.com/excel/events.htm

 

[Guest]

Worked great and thanks for the website. Very informative. I think we're in
the home stretch. I now get two copies. One with just the rows I want, and
one with all 500. I think what is happening is the macro runs, executes
hide_print_unhide, and then executes the print command. Ant suggestions on a
cancel print command or maybe a way to just execute a the hide and then end
the code to allow to got to print?

 

[Tom Ogilvy]

Forgot the sub includes a print command

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If lcase(Activesheet.Name) = "quotations" then
application.EnableEvents = False
Hide_Print_Unhide
application.EnableEvents = True
Cancel = True
End if
End Sub

 

[Guest]

It's perfect now. Thanks for all your help.

Forgot the sub includes a print command

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If lcase(Activesheet.Name) = "quotations" then
application.EnableEvents = False
Hide_Print_Unhide
application.EnableEvents = True
Cancel = True
End if
End Sub