First, reading Don Guillett's post reminded me that cells with formulas in
them, even if empty looking, aren't actually empty, and now we find you need
this to run from a button. First replace the BeforePrint() code with this,
so that things get cleaned up even if the user goes to the Carlog sheet and
prints from it rather than from your button:
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.
Note that I used .UsedRange to find the "bottom" of the data on Carlog, not
the more typical .End(xlUp) function that Don did. Reason for that is that I
presume there could be information in other cells on a row below the last one
with a date in it in column A that you may also want to hide and that
possibly you haven't extended your formula on down in column A. But his
method could probably be used just as effectively. Note that if you're doing
this from a sheet other than the Carlog sheet, you'll need to specify the
Worksheet name as I have in order for it to work.
pano said:
You could put this code into the Workbook_BeforePrint event handler:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
'you could also put this same code into the
'worksheet's _Activate() event handler
Dim mySpecialRange 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
mySpecialRange.SpecialCells(xlBlanks).EntireRow.Hidden = True
End Sub
to get to the proper place to put it, right-click on the Excel icon
immediately left of the word File in the menu toolbar and choose [View Code]
from the list, cut the above and paste it in there.
You'll notice that before hiding the rows, I unhide them. That's in case
some that were hidden previously now have data in column A - this makes sure
things are all up to date before printing.
pano said:
Hi all,
How would I go about hiding rows as a result of a blank in column
A(there is a formula in columnA which gives a blank) and a 0 in column
B, there is blank cells between 4/3/07 and 5/3/07. I would most likely
only do this at the time of printing as the user does not see this
sheet it is all auto filled from data on other sheets. The sheet is
called Carlog.
A B C D
1/3/07 0 0
2/3/07 0700 1526 8.26
3/3/07 0700 1526 8.26
4/3/07 0700 1526 8.26
5/3/07 0700 1526 8.26
6/3/07 0700 1526 8.26
thanks
Stephen- Hide quoted text -
- Show quoted text -
Ok I found the spot to put the code in This workbook, could you give
me some hint on how to print the carlog sheet out now, I wanted to
attach the macro to a button on a menu sheet
thanks
