Hiding Rows when printing

P

pano

Hi, now J Latham provided this to me, I am no WHIZZ with formulas and
have finally found out where to put it. I wonder if some kind person
could help me out. Now this works without any run time errors but
does'nt do what it is supposed to do the whole sheet comes up empty
rows as well

The column and rows I wish to check and hide before printing are cells
J10 to J44 now each cell has a formula in it which picks up a postcode
from another sheet. If a cell from J10 to J44 does not have that
postcode in it I want the entire row hidden so it wont print.

A big thanks to the helpee
Stephen

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim mySpecialRange As Range
Dim anyCell As Range


Set mySpecialRange = Worksheets("Carlog"). _
Range("A1:A" & Worksheets("Carlog").UsedRange.Rows.Count)
'unhide any that may now have data in them
mySpecialRange.EntireRow.Hidden = False
'hide rows based on blanks in column A
'but cells have formulas in them so...
For Each anyCell In mySpecialRange
If anyCell = "" Then
anyCell.EntireRow.Hidden = True
End If
Next
End Sub


Now, all that needs to be done from your button is to tell Excel to
print
that sheet:


Sub PrepAndPrintCarlogSheet()
Sheets("Carlog").PrintOut
End Sub


Assign that macro to the button and you're done.
 
D

Dave Peterson

Check your previous thread.
Hi, now J Latham provided this to me, I am no WHIZZ with formulas and
have finally found out where to put it. I wonder if some kind person
could help me out. Now this works without any run time errors but
does'nt do what it is supposed to do the whole sheet comes up empty
rows as well

The column and rows I wish to check and hide before printing are cells
J10 to J44 now each cell has a formula in it which picks up a postcode
from another sheet. If a cell from J10 to J44 does not have that
postcode in it I want the entire row hidden so it wont print.

A big thanks to the helpee
Stephen

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim mySpecialRange As Range
Dim anyCell As Range

Set mySpecialRange = Worksheets("Carlog"). _
Range("A1:A" & Worksheets("Carlog").UsedRange.Rows.Count)
'unhide any that may now have data in them
mySpecialRange.EntireRow.Hidden = False
'hide rows based on blanks in column A
'but cells have formulas in them so...
For Each anyCell In mySpecialRange
If anyCell = "" Then
anyCell.EntireRow.Hidden = True
End If
Next
End Sub

Now, all that needs to be done from your button is to tell Excel to
print
that sheet:

Sub PrepAndPrintCarlogSheet()
Sheets("Carlog").PrintOut
End Sub

Assign that macro to the button and you're done.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top