Hi
I've small cost sheet which consist of 75 rows and 8
columns. Sheet will look like below:
A B C
Description Code Amount
-------------------------------
Salary 301 5000.00
OT/Bonus 302 0.00
-------------------------------
Total 5000.00

what I Want is in my printout second row i.e. OT/Bonus
which is having "0" Value will hide.
Please give me the better solution.

Wahab

If you want this to work on one sheet put it in the sheet
code module (right-click sheet nametab, View code)

put it in the Workbook module to work on more than one
sheet.

Sub Before_print()
Dim i As Long, nr As Long
Dim c, rng As Range
With Sheets("Sheet2")
nr = Application.WorksheetFunction.CountA(Range("A:A"))
For i = 2 To nr
If IsEmpty(Cells(i, 2)) Or Cells(i, 2) = 0 Then
Cells(i, 2).EntireRow.Hidden = True
End If
Next i
End With
End Sub


Peter
-----Original Message-----
Hi
I've small cost sheet which consist of 75 rows and 8
columns. Sheet will look like below:
A B C
Description Code Amount
-------------------------------
Salary 301 5000.00
OT/Bonus 302 0.00
-------------------------------
Total 5000.00

what I Want is in my printout second row i.e. OT/Bonus
which is having "0" Value will hide.
Please give me the better solution.
.

"Peter Atherton" wrote in message
...
Wahab

If you want this to work on one sheet put it in the sheet
code module (right-click sheet nametab, View code)


BeforePrint is not a worksheet event, it has to go in as a workbook event.


put it in the Workbook module to work on more than one
sheet.

Sub Before_print()

The correct syntax is

Private Sub Workbook_Before(Cancel As Boolean)

And another try for the syntax :-)
Private Sub Workbook_BeforePrint(Cancel As Boolean)

Frank

Bob Phillips wrote:
"Peter Atherton" wrote in message
...
Wahab

If you want this to work on one sheet put it in the sheet
code module (right-click sheet nametab, View code)


BeforePrint is not a worksheet event, it has to go in as a workbook
event.


put it in the Workbook module to work on more than one
sheet.

Sub Before_print()

The correct syntax is

Private Sub Workbook_Before(Cancel As Boolean)

Whoops, where did that go?

How embarrassing<vbg

Bob

"Frank Kabel" wrote in message
...
And another try for the syntax :-)
Private Sub Workbook_BeforePrint(Cancel As Boolean)

Frank

Bob

-----Original Message-----
Whoops, where did that go?

How embarrassing<vbg

Bob

"Frank Kabel" wrote in message
...
And another try for the syntax :-)
Private Sub Workbook_BeforePrint(Cancel As Boolean)

Frank

Bob


.
Thank you very much Peter and Bob.
This work very fine with me, is ther any code that after
printing it will unhide the row?
Or the same thing can I wirte not to print only, like in
lotus when we put || in strating line that line will eject
from printing, thats all no necessity to unhide.
Again thank you very much , this time also I need quick
reply.

Thank you very much Peter and Bob.
This work very fine with me, is ther any code that after
printing it will unhide the row?

There is no AfterPrint event, so you would need to explicitly call any such
macro.

Or the same thing can I wirte not to print only, like in
lotus when we put || in strating line that line will eject
from printing, thats all no necessity to unhide.
Again thank you very much , this time also I need quick
reply.

No that I know of.

No need for an explicit call - make it part of the BeforePrint macro:


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
Application.EnableEvents = False
For Each wkSht In ActiveWindow.SelectedSheets
With wkSht
If .Name = "Sheet2" Then
.Select
.Cells.AutoFilter _
Field:=1, _
Criteria1:="<0"
.PrintOut Preview:=True
Selection.AutoFilter
Else
.PrintOut Preview:=True
End If
End With
Next wkSht
Application.EnableEvents = True
Cancel = True
End Sub




In article ,
"Bob Phillips" wrote:

Thank you very much Peter and Bob.
This work very fine with me, is ther any code that after
printing it will unhide the row?

There is no AfterPrint event, so you would need to explicitly call any such
macro.

I don't think this is what is wanted. The OP wants to hide empty rows for
printing, and then unhide them afterwards. AFAICS this just unhides any
hidden rows before printing.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"JE McGimpsey" wrote in message
...
No need for an explicit call - make it part of the BeforePrint macro:


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
Application.EnableEvents = False
For Each wkSht In ActiveWindow.SelectedSheets
With wkSht
If .Name = "Sheet2" Then
.Select
.Cells.AutoFilter _
Field:=1, _
Criteria1:="<0"
.PrintOut Preview:=True
Selection.AutoFilter
Else
.PrintOut Preview:=True
End If
End With
Next wkSht
Application.EnableEvents = True
Cancel = True
End Sub

Bob,

It looks to me to do what you say the OP wants.

If the sheet name is Sheet2
It applies the autofilter, filtering out rows with zero
prints
removes the autofilter

Perhaps Field should be 3 to correspond to amount, but the concept appears
sound.

Maybe a closer look is called for.

--
Regards,
Tom Ogilvy


Bob Phillips wrote in message
...
I don't think this is what is wanted. The OP wants to hide empty rows for
printing, and then unhide them afterwards. AFAICS this just unhides any
hidden rows before printing.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"JE McGimpsey" wrote in message
...
No need for an explicit call - make it part of the BeforePrint macro:


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
Application.EnableEvents = False
For Each wkSht In ActiveWindow.SelectedSheets
With wkSht
If .Name = "Sheet2" Then
.Select
.Cells.AutoFilter _
Field:=1, _
Criteria1:="<0"
.PrintOut Preview:=True
Selection.AutoFilter
Else
.PrintOut Preview:=True
End If
End With
Next wkSht
Application.EnableEvents = True
Cancel = True
End Sub

AFAICS this just unhides any hidden rows before printing.

I'm not sure what you're looking at, then - did you try it?

The Autofilter hides the rows for which the value = 0 (which is what the
OP specified, not "empty" rows), then the sheet is printed. The
Autofilter is then removed.

OTOH, going back to the original post, the OP specified column C, not A,
so

Field:=1, _

should be

Field:=3, _

I also inadvertently left in the "Preview:=True" arguments that I put in
for testing. Remove them to print hard copies.



In article ,
"Bob Phillips" wrote:

I don't think this is what is wanted. The OP wants to hide empty rows for
printing, and then unhide them afterwards. AFAICS this just unhides any
hidden rows before printing.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"JE McGimpsey" wrote in message
...
No need for an explicit call - make it part of the BeforePrint macro:


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
Application.EnableEvents = False
For Each wkSht In ActiveWindow.SelectedSheets
With wkSht
If .Name = "Sheet2" Then
.Select
.Cells.AutoFilter _
Field:=1, _
Criteria1:="<0"
.PrintOut Preview:=True
Selection.AutoFilter
Else
.PrintOut Preview:=True
End If
End With
Next wkSht
Application.EnableEvents = True
Cancel = True
End Sub

-----Original Message-----
No need for an explicit call - make it part of the
BeforePrint macro:


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
Application.EnableEvents = False
For Each wkSht In ActiveWindow.SelectedSheets
With wkSht
If .Name = "Sheet2" Then
.Select
.Cells.AutoFilter _
Field:=1, _
Criteria1:="<0"
.PrintOut Preview:=True
Selection.AutoFilter
Else
.PrintOut Preview:=True
End If
End With
Next wkSht
Application.EnableEvents = True
Cancel = True
End Sub


Thank you very much, this is what exactly I want.
Wahab
K8