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

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

--
When you lose your mind, you free your life.


"13 Whistling Pigs" wrote:

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

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

--
Regards,
Tom Ogilvy


"13 Whistling Pigs" wrote in
message ...
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

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" wrote:

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

--
Regards,
Tom Ogilvy


"13 Whistling Pigs" wrote in
message ...
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

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.

--
Regards,
Tom Ogilvy

"13 Whistling Pigs" wrote in
message ...
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" wrote:

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

--
Regards,
Tom Ogilvy


"13 Whistling Pigs" wrote in
message ...
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

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.
--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"13 Whistling Pigs" wrote in
message ...
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" wrote:

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

--
Regards,
Tom Ogilvy


"13 Whistling Pigs" wrote
in
message ...
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

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" wrote:

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.
--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"13 Whistling Pigs" wrote in
message ...
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" wrote:

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

--
Regards,
Tom Ogilvy


"13 Whistling Pigs" wrote
in
message ...
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

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

--
Regards,
Tom Ogilvy


"13 Whistling Pigs" wrote in
message ...
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" wrote:

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.
--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"13 Whistling Pigs" wrote
in
message ...
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" wrote:

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

--
Regards,
Tom Ogilvy


"13 Whistling Pigs"
wrote
in
message ...
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

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" wrote:

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

--
Regards,
Tom Ogilvy


"13 Whistling Pigs" wrote in
message ...
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" wrote:

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.
--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"13 Whistling Pigs" wrote
in
message ...
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" wrote:

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

--
Regards,
Tom Ogilvy


"13 Whistling Pigs"
wrote
in
message ...
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

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

--
Regards,
Tom Ogilvy


"13 Whistling Pigs" wrote in
message ...
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" wrote:

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

--
Regards,
Tom Ogilvy


"13 Whistling Pigs" wrote in
message ...
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" wrote:

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.
--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"13 Whistling Pigs"
wrote
in
message ...
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" wrote:

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

--
Regards,
Tom Ogilvy


"13 Whistling Pigs"

wrote
in
message
...
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

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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"13 Whistling Pigs" wrote in
message ...
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" wrote:

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

--
Regards,
Tom Ogilvy


"13 Whistling Pigs" wrote in
message ...
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" wrote:

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.
--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"13 Whistling Pigs"
wrote
in
message ...
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" wrote:

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

--
Regards,
Tom Ogilvy


"13 Whistling Pigs"

wrote
in
message
...
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