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

On Feb 11, 3:13 pm, "pano" wrote:
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

Ok have found this in the archives and modified it to suit can anyone
see anything worng with this, I dont need it to fall over when needed.
I guess I put the printing part after the hiding row part????? and
pres the button and away we go.??

Sub HideRows()
Dim i As Long
Application.ScreenUpdating = False
For i = 10 To 44
For j = 10 To 44
If Range("A" & i).Value = "" Then
Range("A" & i).EntireRow.Hidden = True
End If
If Range("b" & j).Value = 0 Then
Range("b" & j).EntireRow.Hidden = True
End If
Next 'i
Next 'j
Application.ScreenUpdating = True
End Sub

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.Hi dden = 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" wrote:

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

On Feb 11, 4:43 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
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.Hi dden = 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" wrote:
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

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

On Feb 11, 4:43 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
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.Hi dden = 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" wrote:
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

try this. You can assign or better to put in the Before_print of the
ThisWorkbook module.

Sub hiderowsifformulawithblank()
Rows.Hidden = False
lr = Cells(Rows.Count, "a").End(xlUp).Row
For i = 2 To lr
If Cells(i, 1).HasFormula And _
IsNumeric(Cells(i, 1)) = False Then _
Rows(i).Hidden = True
Next i
End Sub

--
Don Guillett
SalesAid Software

"pano" wrote in message
oups.com...
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