Hiding "0" rows in printing.

  • Thread starter Thread starter Wahab
  • Start date Start date
W

Wahab

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
 
Peter Atherton said:
Wahab

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

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()
Click to expand...

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
 
-----Original Message-----
Whoops, where did that go?

How embarrassing<vbg>

Bob




.
Click to expand...
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?
Click to expand...

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.
Click to expand...

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
 
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)
 
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.
 
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.
 
-----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
Click to expand...

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