Removing Rows for Printing
 

I have a report that pulls data from another worksheet in Range A26:J58.
What I want to be able to do is assign a button for printing the report but
before printing from A1:J70 removing or hiding any row in the A26:J58 range
where there is no data. The range is filled from row 26 down so it is not a
random fill.

Also, I want to save this workbook as a template so that it can be used over
again, so I guess it would not be good to delete the rows in the range
otherwise I would have to recreate them.

Thanks for your help.

Removing Rows for Printing
 

Hi Frick

Start here
http://www.rondebruin.nl/print.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
I have a report that pulls data from another worksheet in Range A26:J58. What I want to be able to do is assign a button for
printing the report but before printing from A1:J70 removing or hiding any row in the A26:J58 range where there is no data. The
range is filled from row 26 down so it is not a random fill.

Also, I want to save this workbook as a template so that it can be used over again, so I guess it would not be good to delete the
rows in the range otherwise I would have to recreate them.

Thanks for your help.

Removing Rows for Printing
 

Hi Ron,

Thanks for the link. From the link I used the following script:

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A26:A26")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

I wanted the macro to use just Col A for the test. I have a formula in each
row in Col A that is a IF formula that states that If the reference cell is
0 enter the reference cell otherwise enter 0.

I set up a test range from row 26 to 58 with rows 40 through 58 =0. So,
those rows should not have been included in the printed report in range
A1:J81. It did not work and all the rows were there.

Can you explain my error.

Thanks,


"Ron de Bruin" wrote in message
...
Hi Frick

Start here
http://www.rondebruin.nl/print.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
I have a report that pulls data from another worksheet in Range A26:J58.
What I want to be able to do is assign a button for printing the report
but before printing from A1:J70 removing or hiding any row in the A26:J58
range where there is no data. The range is filled from row 26 down so it
is not a random fill.

Also, I want to save this workbook as a template so that it can be used
over again, so I guess it would not be good to delete the rows in the
range otherwise I would have to recreate them.

Thanks for your help.

Removing Rows for Printing
 

Hi Frick

You must use this to test the cells in A:G if they are empty

.Cells(rw, 1).Range("A1:G1")) = 0 Then _

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Hi Ron,

Thanks for the link. From the link I used the following script:

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A26:A26")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

I wanted the macro to use just Col A for the test. I have a formula in each row in Col A that is a IF formula that states that If
the reference cell is
0 enter the reference cell otherwise enter 0.

I set up a test range from row 26 to 58 with rows 40 through 58 =0. So, those rows should not have been included in the printed
report in range A1:J81. It did not work and all the rows were there.

Can you explain my error.

Thanks,


"Ron de Bruin" wrote in message ...
Hi Frick

Start here
http://www.rondebruin.nl/print.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
I have a report that pulls data from another worksheet in Range A26:J58. What I want to be able to do is assign a button for
printing the report but before printing from A1:J70 removing or hiding any row in the A26:J58 range where there is no data. The
range is filled from row 26 down so it is not a random fill.

Also, I want to save this workbook as a template so that it can be used over again, so I guess it would not be good to delete
the rows in the range otherwise I would have to recreate them.

Thanks for your help.

Removing Rows for Printing
 

Ron,

I want to hide those rows where the value in Col A=0 for rows 26 through 58.

In your script you have A1:G1 so I think that would mean that ALL cells in
Col's A through G would have to be empty which may not be the case.
However, if the the cell in col A =0 then regardless of what value might be
in any other column I would want the row hidden when printing.

I tried your modified script and it still does nto hide any rows.

Any further thoughts?

Frick

"Ron de Bruin" wrote in message
...
Hi Frick

You must use this to test the cells in A:G if they are empty

.Cells(rw, 1).Range("A1:G1")) = 0 Then _

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Hi Ron,

Thanks for the link. From the link I used the following script:

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A26:A26")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

I wanted the macro to use just Col A for the test. I have a formula in
each row in Col A that is a IF formula that states that If the reference
cell is
0 enter the reference cell otherwise enter 0.

I set up a test range from row 26 to 58 with rows 40 through 58 =0. So,
those rows should not have been included in the printed report in range
A1:J81. It did not work and all the rows were there.

Can you explain my error.

Thanks,


"Ron de Bruin" wrote in message
...
Hi Frick

Start here
http://www.rondebruin.nl/print.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
I have a report that pulls data from another worksheet in Range A26:J58.
What I want to be able to do is assign a button for printing the report
but before printing from A1:J70 removing or hiding any row in the
A26:J58 range where there is no data. The range is filled from row 26
down so it is not a random fill.

Also, I want to save this workbook as a template so that it can be used
over again, so I guess it would not be good to delete the rows in the
range otherwise I would have to recreate them.

Thanks for your help.

Removing Rows for Printing
 

Test the macro in this thread it will test only the cells in A

I posted this

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

I want to hide those rows where the value in Col A=0 for rows 26 through 58.

In your script you have A1:G1 so I think that would mean that ALL cells in Col's A through G would have to be empty which may not
be the case. However, if the the cell in col A =0 then regardless of what value might be in any other column I would want the row
hidden when printing.

I tried your modified script and it still does nto hide any rows.

Any further thoughts?

Frick

"Ron de Bruin" wrote in message ...
Hi Frick

You must use this to test the cells in A:G if they are empty

.Cells(rw, 1).Range("A1:G1")) = 0 Then _

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Hi Ron,

Thanks for the link. From the link I used the following script:

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A26:A26")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

I wanted the macro to use just Col A for the test. I have a formula in each row in Col A that is a IF formula that states that
If the reference cell is
0 enter the reference cell otherwise enter 0.

I set up a test range from row 26 to 58 with rows 40 through 58 =0. So, those rows should not have been included in the printed
report in range A1:J81. It did not work and all the rows were there.

Can you explain my error.

Thanks,


"Ron de Bruin" wrote in message ...
Hi Frick

Start here
http://www.rondebruin.nl/print.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
I have a report that pulls data from another worksheet in Range A26:J58. What I want to be able to do is assign a button for
printing the report but before printing from A1:J70 removing or hiding any row in the A26:J58 range where there is no data.
The range is filled from row 26 down so it is not a random fill.

Also, I want to save this workbook as a template so that it can be used over again, so I guess it would not be good to delete
the rows in the range otherwise I would have to recreate them.

Thanks for your help.

Removing Rows for Printing
 

Ron,

Using:

Cells(rw, 1).Range("A1:G1")) = 0 Then _

I pasted it and then hit the print preview button. Nothing happens, all
rows still show in the preview.
I checked the formulas in Col A and confirm that each sell either returns a
value greater then 0 or 0.

So now I am totally lost.

The formula is col A from 26 to 58 is:
IF('Main Summary'!B19=0,0,'Main Summary'!B19)

Also each cell in Col A26 through 58 has a border, but I would not think
that has any concern.

Where to now?


"Ron de Bruin" wrote in message
...
Test the macro in this thread it will test only the cells in A

I posted this

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

I want to hide those rows where the value in Col A=0 for rows 26 through
58.

In your script you have A1:G1 so I think that would mean that ALL cells
in Col's A through G would have to be empty which may not be the case.
However, if the the cell in col A =0 then regardless of what value might
be in any other column I would want the row hidden when printing.

I tried your modified script and it still does nto hide any rows.

Any further thoughts?

Frick

"Ron de Bruin" wrote in message
...
Hi Frick

You must use this to test the cells in A:G if they are empty

.Cells(rw, 1).Range("A1:G1")) = 0 Then _

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Hi Ron,

Thanks for the link. From the link I used the following script:

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A26:A26")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

I wanted the macro to use just Col A for the test. I have a formula in
each row in Col A that is a IF formula that states that If the
reference cell is
0 enter the reference cell otherwise enter 0.

I set up a test range from row 26 to 58 with rows 40 through 58 =0.
So, those rows should not have been included in the printed report in
range A1:J81. It did not work and all the rows were there.

Can you explain my error.

Thanks,


"Ron de Bruin" wrote in message
...
Hi Frick

Start here
http://www.rondebruin.nl/print.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
I have a report that pulls data from another worksheet in Range
A26:J58. What I want to be able to do is assign a button for printing
the report but before printing from A1:J70 removing or hiding any row
in the A26:J58 range where there is no data. The range is filled from
row 26 down so it is not a random fill.

Also, I want to save this workbook as a template so that it can be
used over again, so I guess it would not be good to delete the rows
in the range otherwise I would have to recreate them.

Thanks for your help.

Removing Rows for Printing
 

Read good

I posted this macro


Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

Using:

Cells(rw, 1).Range("A1:G1")) = 0 Then _

I pasted it and then hit the print preview button. Nothing happens, all rows still show in the preview.
I checked the formulas in Col A and confirm that each sell either returns a value greater then 0 or 0.

So now I am totally lost.

The formula is col A from 26 to 58 is:
IF('Main Summary'!B19=0,0,'Main Summary'!B19)

Also each cell in Col A26 through 58 has a border, but I would not think that has any concern.

Where to now?


"Ron de Bruin" wrote in message ...
Test the macro in this thread it will test only the cells in A

I posted this

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

I want to hide those rows where the value in Col A=0 for rows 26 through 58.

In your script you have A1:G1 so I think that would mean that ALL cells in Col's A through G would have to be empty which may
not be the case. However, if the the cell in col A =0 then regardless of what value might be in any other column I would want
the row hidden when printing.

I tried your modified script and it still does nto hide any rows.

Any further thoughts?

Frick

"Ron de Bruin" wrote in message ...
Hi Frick

You must use this to test the cells in A:G if they are empty

.Cells(rw, 1).Range("A1:G1")) = 0 Then _

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Hi Ron,

Thanks for the link. From the link I used the following script:

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A26:A26")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

I wanted the macro to use just Col A for the test. I have a formula in each row in Col A that is a IF formula that states
that If the reference cell is
0 enter the reference cell otherwise enter 0.

I set up a test range from row 26 to 58 with rows 40 through 58 =0. So, those rows should not have been included in the
printed report in range A1:J81. It did not work and all the rows were there.

Can you explain my error.

Thanks,


"Ron de Bruin" wrote in message ...
Hi Frick

Start here
http://www.rondebruin.nl/print.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
I have a report that pulls data from another worksheet in Range A26:J58. What I want to be able to do is assign a button for
printing the report but before printing from A1:J70 removing or hiding any row in the A26:J58 range where there is no data.
The range is filled from row 26 down so it is not a random fill.

Also, I want to save this workbook as a template so that it can be used over again, so I guess it would not be good to
delete the rows in the range otherwise I would have to recreate them.

Thanks for your help.

Removing Rows for Printing
 

Ron,

Sorry, the script that you posted below in your last response is the script
that I used. Still does not make any difference.

Frick


"Ron de Bruin" wrote in message
...
Read good

I posted this macro


Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

Using:

Cells(rw, 1).Range("A1:G1")) = 0 Then _

I pasted it and then hit the print preview button. Nothing happens, all
rows still show in the preview.
I checked the formulas in Col A and confirm that each sell either returns
a value greater then 0 or 0.

So now I am totally lost.

The formula is col A from 26 to 58 is:
IF('Main Summary'!B19=0,0,'Main Summary'!B19)

Also each cell in Col A26 through 58 has a border, but I would not think
that has any concern.

Where to now?


"Ron de Bruin" wrote in message
...
Test the macro in this thread it will test only the cells in A

I posted this

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

I want to hide those rows where the value in Col A=0 for rows 26
through 58.

In your script you have A1:G1 so I think that would mean that ALL cells
in Col's A through G would have to be empty which may not be the case.
However, if the the cell in col A =0 then regardless of what value
might be in any other column I would want the row hidden when printing.

I tried your modified script and it still does nto hide any rows.

Any further thoughts?

Frick

"Ron de Bruin" wrote in message
...
Hi Frick

You must use this to test the cells in A:G if they are empty

.Cells(rw, 1).Range("A1:G1")) = 0 Then _

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Hi Ron,

Thanks for the link. From the link I used the following script:

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A26:A26")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

I wanted the macro to use just Col A for the test. I have a formula
in each row in Col A that is a IF formula that states that If the
reference cell is
0 enter the reference cell otherwise enter 0.

I set up a test range from row 26 to 58 with rows 40 through 58 =0.
So, those rows should not have been included in the printed report in
range A1:J81. It did not work and all the rows were there.

Can you explain my error.

Thanks,


"Ron de Bruin" wrote in message
...
Hi Frick

Start here
http://www.rondebruin.nl/print.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
I have a report that pulls data from another worksheet in Range
A26:J58. What I want to be able to do is assign a button for
printing the report but before printing from A1:J70 removing or
hiding any row in the A26:J58 range where there is no data. The
range is filled from row 26 down so it is not a random fill.

Also, I want to save this workbook as a template so that it can be
used over again, so I guess it would not be good to delete the rows
in the range otherwise I would have to recreate them.

Thanks for your help.

Removing Rows for Printing
 

If you want an alternative approach, you could set up a filter to cover
column A only. Then when you are ready to print you use the filter to
select Custom ... | Not Equal To | 0 (zero).

In this way only the rows with non-zero values will be displayed (and
printed).

Hope this helps.

Pete

Removing Rows for Printing
 

Do you understand that you must run the macro with Alt-F8
It will not run automatic when you use the print button

This is also possible but first run the macro with Alt-F8

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

Sorry, the script that you posted below in your last response is the script that I used. Still does not make any difference.

Frick


"Ron de Bruin" wrote in message ...
Read good

I posted this macro


Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

Using:

Cells(rw, 1).Range("A1:G1")) = 0 Then _

I pasted it and then hit the print preview button. Nothing happens, all rows still show in the preview.
I checked the formulas in Col A and confirm that each sell either returns a value greater then 0 or 0.

So now I am totally lost.

The formula is col A from 26 to 58 is:
IF('Main Summary'!B19=0,0,'Main Summary'!B19)

Also each cell in Col A26 through 58 has a border, but I would not think that has any concern.

Where to now?


"Ron de Bruin" wrote in message ...
Test the macro in this thread it will test only the cells in A

I posted this

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

I want to hide those rows where the value in Col A=0 for rows 26 through 58.

In your script you have A1:G1 so I think that would mean that ALL cells in Col's A through G would have to be empty which may
not be the case. However, if the the cell in col A =0 then regardless of what value might be in any other column I would want
the row hidden when printing.

I tried your modified script and it still does nto hide any rows.

Any further thoughts?

Frick

"Ron de Bruin" wrote in message ...
Hi Frick

You must use this to test the cells in A:G if they are empty

.Cells(rw, 1).Range("A1:G1")) = 0 Then _

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Hi Ron,

Thanks for the link. From the link I used the following script:

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A26:A26")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

I wanted the macro to use just Col A for the test. I have a formula in each row in Col A that is a IF formula that states
that If the reference cell is
0 enter the reference cell otherwise enter 0.

I set up a test range from row 26 to 58 with rows 40 through 58 =0. So, those rows should not have been included in the
printed report in range A1:J81. It did not work and all the rows were there.

Can you explain my error.

Thanks,


"Ron de Bruin" wrote in message ...
Hi Frick

Start here
http://www.rondebruin.nl/print.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
I have a report that pulls data from another worksheet in Range A26:J58. What I want to be able to do is assign a button
for printing the report but before printing from A1:J70 removing or hiding any row in the A26:J58 range where there is no
data. The range is filled from row 26 down so it is not a random fill.

Also, I want to save this workbook as a template so that it can be used over again, so I guess it would not be good to
delete the rows in the range otherwise I would have to recreate them.

Thanks for your help.

Removing Rows for Printing
 

Ron,

Sorry for my ignorance! I was unaware that I need to run the macro first
with Alt-F8. Now, having done that, yes the rows are removed for prinitng.

How do I now automate it. I can tell you that I do know how to create a
button and attach the script to it. So what must be added to the script so
that when I select a created "Print Button" on my main page it will print
the report on the "Report" page with the rows removed with 0 value.

Thank you again for all your time and patience.

Frick

"Ron de Bruin" wrote in message
...
Do you understand that you must run the macro with Alt-F8
It will not run automatic when you use the print button

This is also possible but first run the macro with Alt-F8

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

Sorry, the script that you posted below in your last response is the
script that I used. Still does not make any difference.

Frick


"Ron de Bruin" wrote in message
...
Read good

I posted this macro


Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

Using:

Cells(rw, 1).Range("A1:G1")) = 0 Then _

I pasted it and then hit the print preview button. Nothing happens,
all rows still show in the preview.
I checked the formulas in Col A and confirm that each sell either
returns a value greater then 0 or 0.

So now I am totally lost.

The formula is col A from 26 to 58 is:
IF('Main Summary'!B19=0,0,'Main Summary'!B19)

Also each cell in Col A26 through 58 has a border, but I would not
think that has any concern.

Where to now?


"Ron de Bruin" wrote in message
...
Test the macro in this thread it will test only the cells in A

I posted this

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

I want to hide those rows where the value in Col A=0 for rows 26
through 58.

In your script you have A1:G1 so I think that would mean that ALL
cells in Col's A through G would have to be empty which may not be
the case. However, if the the cell in col A =0 then regardless of
what value might be in any other column I would want the row hidden
when printing.

I tried your modified script and it still does nto hide any rows.

Any further thoughts?

Frick

"Ron de Bruin" wrote in message
...
Hi Frick

You must use this to test the cells in A:G if they are empty

.Cells(rw, 1).Range("A1:G1")) = 0 Then _

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Hi Ron,

Thanks for the link. From the link I used the following script:

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A26:A26")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

I wanted the macro to use just Col A for the test. I have a
formula in each row in Col A that is a IF formula that states that
If the reference cell is
0 enter the reference cell otherwise enter 0.

I set up a test range from row 26 to 58 with rows 40 through 58 =0.
So, those rows should not have been included in the printed report
in range A1:J81. It did not work and all the rows were there.

Can you explain my error.

Thanks,


"Ron de Bruin" wrote in message
...
Hi Frick

Start here
http://www.rondebruin.nl/print.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
I have a report that pulls data from another worksheet in Range
A26:J58. What I want to be able to do is assign a button for
printing the report but before printing from A1:J70 removing or
hiding any row in the A26:J58 range where there is no data. The
range is filled from row 26 down so it is not a random fill.

Also, I want to save this workbook as a template so that it can
be used over again, so I guess it would not be good to delete the
rows in the range otherwise I would have to recreate them.

Thanks for your help.

Removing Rows for Printing
 

Hi Frick

We can use a event that run when you press the print button in Excel
Copy this event in the Thisworkbook module of your workbook

Then press the print button in the toolbar
Change PrintPreview to Printout in the code if it is working OK


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rw As Long
If ActiveSheet.Name = "Report" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintPreview ' for testing use .PrintPreview
.Range("A26:A58").EntireRow.Hidden = False
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

Sorry for my ignorance! I was unaware that I need to run the macro first with Alt-F8. Now, having done that, yes the rows are
removed for prinitng.

How do I now automate it. I can tell you that I do know how to create a button and attach the script to it. So what must be
added to the script so that when I select a created "Print Button" on my main page it will print the report on the "Report" page
with the rows removed with 0 value.

Thank you again for all your time and patience.

Frick

"Ron de Bruin" wrote in message ...
Do you understand that you must run the macro with Alt-F8
It will not run automatic when you use the print button

This is also possible but first run the macro with Alt-F8

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

Sorry, the script that you posted below in your last response is the script that I used. Still does not make any difference.

Frick


"Ron de Bruin" wrote in message ...
Read good

I posted this macro


Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

Using:

Cells(rw, 1).Range("A1:G1")) = 0 Then _

I pasted it and then hit the print preview button. Nothing happens, all rows still show in the preview.
I checked the formulas in Col A and confirm that each sell either returns a value greater then 0 or 0.

So now I am totally lost.

The formula is col A from 26 to 58 is:
IF('Main Summary'!B19=0,0,'Main Summary'!B19)

Also each cell in Col A26 through 58 has a border, but I would not think that has any concern.

Where to now?


"Ron de Bruin" wrote in message ...
Test the macro in this thread it will test only the cells in A

I posted this

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

I want to hide those rows where the value in Col A=0 for rows 26 through 58.

In your script you have A1:G1 so I think that would mean that ALL cells in Col's A through G would have to be empty which
may not be the case. However, if the the cell in col A =0 then regardless of what value might be in any other column I would
want the row hidden when printing.

I tried your modified script and it still does nto hide any rows.

Any further thoughts?

Frick

"Ron de Bruin" wrote in message ...
Hi Frick

You must use this to test the cells in A:G if they are empty

.Cells(rw, 1).Range("A1:G1")) = 0 Then _

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Hi Ron,

Thanks for the link. From the link I used the following script:

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A26:A26")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

I wanted the macro to use just Col A for the test. I have a formula in each row in Col A that is a IF formula that states
that If the reference cell is
0 enter the reference cell otherwise enter 0.

I set up a test range from row 26 to 58 with rows 40 through 58 =0. So, those rows should not have been included in the
printed report in range A1:J81. It did not work and all the rows were there.

Can you explain my error.

Thanks,


"Ron de Bruin" wrote in message ...
Hi Frick

Start here
http://www.rondebruin.nl/print.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
I have a report that pulls data from another worksheet in Range A26:J58. What I want to be able to do is assign a button
for printing the report but before printing from A1:J70 removing or hiding any row in the A26:J58 range where there is no
data. The range is filled from row 26 down so it is not a random fill.

Also, I want to save this workbook as a template so that it can be used over again, so I guess it would not be good to
delete the rows in the range otherwise I would have to recreate them.

Thanks for your help.

Removing Rows for Printing
 

Ron,

I need to execute the printing from a Print Button on my Main Summary page.
This way I can even hide the Report page so that no has to even see it.
They just complete the report from the Main Summary page and then when
finished click on the Print Report Button.

So how can that be handled in the scripting?

Frick


"Ron de Bruin" wrote in message
...
Hi Frick

We can use a event that run when you press the print button in Excel
Copy this event in the Thisworkbook module of your workbook

Then press the print button in the toolbar
Change PrintPreview to Printout in the code if it is working OK


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rw As Long
If ActiveSheet.Name = "Report" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintPreview ' for testing use .PrintPreview
.Range("A26:A58").EntireRow.Hidden = False
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

Sorry for my ignorance! I was unaware that I need to run the macro first
with Alt-F8. Now, having done that, yes the rows are removed for
prinitng.

How do I now automate it. I can tell you that I do know how to create a
button and attach the script to it. So what must be added to the script
so that when I select a created "Print Button" on my main page it will
print the report on the "Report" page with the rows removed with 0 value.

Thank you again for all your time and patience.

Frick

"Ron de Bruin" wrote in message
...
Do you understand that you must run the macro with Alt-F8
It will not run automatic when you use the print button

This is also possible but first run the macro with Alt-F8

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

Sorry, the script that you posted below in your last response is the
script that I used. Still does not make any difference.

Frick


"Ron de Bruin" wrote in message
...
Read good

I posted this macro


Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

Using:

Cells(rw, 1).Range("A1:G1")) = 0 Then _

I pasted it and then hit the print preview button. Nothing happens,
all rows still show in the preview.
I checked the formulas in Col A and confirm that each sell either
returns a value greater then 0 or 0.

So now I am totally lost.

The formula is col A from 26 to 58 is:
IF('Main Summary'!B19=0,0,'Main Summary'!B19)

Also each cell in Col A26 through 58 has a border, but I would not
think that has any concern.

Where to now?


"Ron de Bruin" wrote in message
...
Test the macro in this thread it will test only the cells in A

I posted this

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

I want to hide those rows where the value in Col A=0 for rows 26
through 58.

In your script you have A1:G1 so I think that would mean that ALL
cells in Col's A through G would have to be empty which may not be
the case. However, if the the cell in col A =0 then regardless of
what value might be in any other column I would want the row hidden
when printing.

I tried your modified script and it still does nto hide any rows.

Any further thoughts?

Frick

"Ron de Bruin" wrote in message
...
Hi Frick

You must use this to test the cells in A:G if they are empty

.Cells(rw, 1).Range("A1:G1")) = 0 Then _

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Hi Ron,

Thanks for the link. From the link I used the following script:

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A26:A26")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

I wanted the macro to use just Col A for the test. I have a
formula in each row in Col A that is a IF formula that states
that If the reference cell is
0 enter the reference cell otherwise enter 0.

I set up a test range from row 26 to 58 with rows 40 through 58
=0. So, those rows should not have been included in the printed
report in range A1:J81. It did not work and all the rows were
there.

Can you explain my error.

Thanks,


"Ron de Bruin" wrote in message
...
Hi Frick

Start here
http://www.rondebruin.nl/print.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
I have a report that pulls data from another worksheet in Range
A26:J58. What I want to be able to do is assign a button for
printing the report but before printing from A1:J70 removing or
hiding any row in the A26:J58 range where there is no data. The
range is filled from row 26 down so it is not a random fill.

Also, I want to save this workbook as a template so that it can
be used over again, so I guess it would not be good to delete
the rows in the range otherwise I would have to recreate them.

Thanks for your help.

Removing Rows for Printing
 

If you hide it you must unhide it in the before you can print
Attach this macro to the button on the Main Summary page

It will unhide the sheet
hide the rows with a 0 in A
Print
unhide the rows
hide the sheet


Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False
With Sheets("Report")
.Visible = -1
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A26:A58").EntireRow.Hidden = False
.Visible = 0
End With
Application.ScreenUpdating = True
End Sub





--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

I need to execute the printing from a Print Button on my Main Summary page. This way I can even hide the Report page so that no
has to even see it. They just complete the report from the Main Summary page and then when finished click on the Print Report
Button.

So how can that be handled in the scripting?

Frick


"Ron de Bruin" wrote in message ...
Hi Frick

We can use a event that run when you press the print button in Excel
Copy this event in the Thisworkbook module of your workbook

Then press the print button in the toolbar
Change PrintPreview to Printout in the code if it is working OK


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rw As Long
If ActiveSheet.Name = "Report" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintPreview ' for testing use .PrintPreview
.Range("A26:A58").EntireRow.Hidden = False
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

Sorry for my ignorance! I was unaware that I need to run the macro first with Alt-F8. Now, having done that, yes the rows are
removed for prinitng.

How do I now automate it. I can tell you that I do know how to create a button and attach the script to it. So what must be
added to the script so that when I select a created "Print Button" on my main page it will print the report on the "Report" page
with the rows removed with 0 value.

Thank you again for all your time and patience.

Frick

"Ron de Bruin" wrote in message ...
Do you understand that you must run the macro with Alt-F8
It will not run automatic when you use the print button

This is also possible but first run the macro with Alt-F8

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

Sorry, the script that you posted below in your last response is the script that I used. Still does not make any difference.

Frick


"Ron de Bruin" wrote in message ...
Read good

I posted this macro


Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

Using:

Cells(rw, 1).Range("A1:G1")) = 0 Then _

I pasted it and then hit the print preview button. Nothing happens, all rows still show in the preview.
I checked the formulas in Col A and confirm that each sell either returns a value greater then 0 or 0.

So now I am totally lost.

The formula is col A from 26 to 58 is:
IF('Main Summary'!B19=0,0,'Main Summary'!B19)

Also each cell in Col A26 through 58 has a border, but I would not think that has any concern.

Where to now?


"Ron de Bruin" wrote in message ...
Test the macro in this thread it will test only the cells in A

I posted this

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

I want to hide those rows where the value in Col A=0 for rows 26 through 58.

In your script you have A1:G1 so I think that would mean that ALL cells in Col's A through G would have to be empty which
may not be the case. However, if the the cell in col A =0 then regardless of what value might be in any other column I
would want the row hidden when printing.

I tried your modified script and it still does nto hide any rows.

Any further thoughts?

Frick

"Ron de Bruin" wrote in message ...
Hi Frick

You must use this to test the cells in A:G if they are empty

.Cells(rw, 1).Range("A1:G1")) = 0 Then _

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Hi Ron,

Thanks for the link. From the link I used the following script:

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A26:A26")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

I wanted the macro to use just Col A for the test. I have a formula in each row in Col A that is a IF formula that
states that If the reference cell is
0 enter the reference cell otherwise enter 0.

I set up a test range from row 26 to 58 with rows 40 through 58 =0. So, those rows should not have been included in the
printed report in range A1:J81. It did not work and all the rows were there.

Can you explain my error.

Thanks,


"Ron de Bruin" wrote in message ...
Hi Frick

Start here
http://www.rondebruin.nl/print.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
I have a report that pulls data from another worksheet in Range A26:J58. What I want to be able to do is assign a
button for printing the report but before printing from A1:J70 removing or hiding any row in the A26:J58 range where
there is no data. The range is filled from row 26 down so it is not a random fill.

Also, I want to save this workbook as a template so that it can be used over again, so I guess it would not be good to
delete the rows in the range otherwise I would have to recreate them.

Thanks for your help.

Removing Rows for Printing
 

Ron,

I attached the script to the Print Button. I hid the Report worksheet. I
then click on the Print button and got a Compile error: Ambiguous name
detected: Hide_Print_Unhide.

Scott

"Ron de Bruin" wrote in message
...
If you hide it you must unhide it in the before you can print
Attach this macro to the button on the Main Summary page

It will unhide the sheet
hide the rows with a 0 in A
Print
unhide the rows
hide the sheet


Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False
With Sheets("Report")
.Visible = -1
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A26:A58").EntireRow.Hidden = False
.Visible = 0
End With
Application.ScreenUpdating = True
End Sub





--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

I need to execute the printing from a Print Button on my Main Summary
page. This way I can even hide the Report page so that no has to even see
it. They just complete the report from the Main Summary page and then
when finished click on the Print Report Button.

So how can that be handled in the scripting?

Frick


"Ron de Bruin" wrote in message
...
Hi Frick

We can use a event that run when you press the print button in Excel
Copy this event in the Thisworkbook module of your workbook

Then press the print button in the toolbar
Change PrintPreview to Printout in the code if it is working OK


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rw As Long
If ActiveSheet.Name = "Report" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintPreview ' for testing use .PrintPreview
.Range("A26:A58").EntireRow.Hidden = False
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

Sorry for my ignorance! I was unaware that I need to run the macro
first with Alt-F8. Now, having done that, yes the rows are removed for
prinitng.

How do I now automate it. I can tell you that I do know how to create
a button and attach the script to it. So what must be added to the
script so that when I select a created "Print Button" on my main page
it will print the report on the "Report" page with the rows removed
with 0 value.

Thank you again for all your time and patience.

Frick

"Ron de Bruin" wrote in message
...
Do you understand that you must run the macro with Alt-F8
It will not run automatic when you use the print button

This is also possible but first run the macro with Alt-F8

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

Sorry, the script that you posted below in your last response is the
script that I used. Still does not make any difference.

Frick


"Ron de Bruin" wrote in message
...
Read good

I posted this macro


Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

Using:

Cells(rw, 1).Range("A1:G1")) = 0 Then _

I pasted it and then hit the print preview button. Nothing
happens, all rows still show in the preview.
I checked the formulas in Col A and confirm that each sell either
returns a value greater then 0 or 0.

So now I am totally lost.

The formula is col A from 26 to 58 is:
IF('Main Summary'!B19=0,0,'Main Summary'!B19)

Also each cell in Col A26 through 58 has a border, but I would not
think that has any concern.

Where to now?


"Ron de Bruin" wrote in message
...
Test the macro in this thread it will test only the cells in A

I posted this

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

I want to hide those rows where the value in Col A=0 for rows 26
through 58.

In your script you have A1:G1 so I think that would mean that ALL
cells in Col's A through G would have to be empty which may not
be the case. However, if the the cell in col A =0 then regardless
of what value might be in any other column I would want the row
hidden when printing.

I tried your modified script and it still does nto hide any rows.

Any further thoughts?

Frick

"Ron de Bruin" wrote in message
...
Hi Frick

You must use this to test the cells in A:G if they are empty

.Cells(rw, 1).Range("A1:G1")) = 0 Then _

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Hi Ron,

Thanks for the link. From the link I used the following
script:

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A26:A26")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

I wanted the macro to use just Col A for the test. I have a
formula in each row in Col A that is a IF formula that states
that If the reference cell is
0 enter the reference cell otherwise enter 0.

I set up a test range from row 26 to 58 with rows 40 through 58
=0. So, those rows should not have been included in the printed
report in range A1:J81. It did not work and all the rows were
there.

Can you explain my error.

Thanks,


"Ron de Bruin" wrote in message
...
Hi Frick

Start here
http://www.rondebruin.nl/print.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
I have a report that pulls data from another worksheet in
Range A26:J58. What I want to be able to do is assign a button
for printing the report but before printing from A1:J70
removing or hiding any row in the A26:J58 range where there is
no data. The range is filled from row 26 down so it is not a
random fill.

Also, I want to save this workbook as a template so that it
can be used over again, so I guess it would not be good to
delete the rows in the range otherwise I would have to
recreate them.

Thanks for your help.

Removing Rows for Printing
 

You have two macro's with the same name then
Delete the old one

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

I attached the script to the Print Button. I hid the Report worksheet. I then click on the Print button and got a Compile error:
Ambiguous name detected: Hide_Print_Unhide.

Scott

"Ron de Bruin" wrote in message ...
If you hide it you must unhide it in the before you can print
Attach this macro to the button on the Main Summary page

It will unhide the sheet
hide the rows with a 0 in A
Print
unhide the rows
hide the sheet


Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False
With Sheets("Report")
.Visible = -1
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A26:A58").EntireRow.Hidden = False
.Visible = 0
End With
Application.ScreenUpdating = True
End Sub





--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

I need to execute the printing from a Print Button on my Main Summary page. This way I can even hide the Report page so that no
has to even see it. They just complete the report from the Main Summary page and then when finished click on the Print Report
Button.

So how can that be handled in the scripting?

Frick


"Ron de Bruin" wrote in message ...
Hi Frick

We can use a event that run when you press the print button in Excel
Copy this event in the Thisworkbook module of your workbook

Then press the print button in the toolbar
Change PrintPreview to Printout in the code if it is working OK


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rw As Long
If ActiveSheet.Name = "Report" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintPreview ' for testing use .PrintPreview
.Range("A26:A58").EntireRow.Hidden = False
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

Sorry for my ignorance! I was unaware that I need to run the macro first with Alt-F8. Now, having done that, yes the rows are
removed for prinitng.

How do I now automate it. I can tell you that I do know how to create a button and attach the script to it. So what must be
added to the script so that when I select a created "Print Button" on my main page it will print the report on the "Report"
page with the rows removed with 0 value.

Thank you again for all your time and patience.

Frick

"Ron de Bruin" wrote in message ...
Do you understand that you must run the macro with Alt-F8
It will not run automatic when you use the print button

This is also possible but first run the macro with Alt-F8

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

Sorry, the script that you posted below in your last response is the script that I used. Still does not make any
difference.

Frick


"Ron de Bruin" wrote in message ...
Read good

I posted this macro


Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

Using:

Cells(rw, 1).Range("A1:G1")) = 0 Then _

I pasted it and then hit the print preview button. Nothing happens, all rows still show in the preview.
I checked the formulas in Col A and confirm that each sell either returns a value greater then 0 or 0.

So now I am totally lost.

The formula is col A from 26 to 58 is:
IF('Main Summary'!B19=0,0,'Main Summary'!B19)

Also each cell in Col A26 through 58 has a border, but I would not think that has any concern.

Where to now?


"Ron de Bruin" wrote in message ...
Test the macro in this thread it will test only the cells in A

I posted this

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

I want to hide those rows where the value in Col A=0 for rows 26 through 58.

In your script you have A1:G1 so I think that would mean that ALL cells in Col's A through G would have to be empty
which may not be the case. However, if the the cell in col A =0 then regardless of what value might be in any other
column I would want the row hidden when printing.

I tried your modified script and it still does nto hide any rows.

Any further thoughts?

Frick

"Ron de Bruin" wrote in message ...
Hi Frick

You must use this to test the cells in A:G if they are empty

.Cells(rw, 1).Range("A1:G1")) = 0 Then _

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Hi Ron,

Thanks for the link. From the link I used the following script:

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A26:A26")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

I wanted the macro to use just Col A for the test. I have a formula in each row in Col A that is a IF formula that
states that If the reference cell is
0 enter the reference cell otherwise enter 0.

I set up a test range from row 26 to 58 with rows 40 through 58 =0. So, those rows should not have been included in
the printed report in range A1:J81. It did not work and all the rows were there.

Can you explain my error.

Thanks,


"Ron de Bruin" wrote in message ...
Hi Frick

Start here
http://www.rondebruin.nl/print.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
I have a report that pulls data from another worksheet in Range A26:J58. What I want to be able to do is assign a
button for printing the report but before printing from A1:J70 removing or hiding any row in the A26:J58 range where
there is no data. The range is filled from row 26 down so it is not a random fill.

Also, I want to save this workbook as a template so that it can be used over again, so I guess it would not be good
to delete the rows in the range otherwise I would have to recreate them.

Thanks for your help.

Removing Rows for Printing
 

Ron,

I have two buttons on the main worksheet. One is to add rows on the main
worksheet and the other is the Print Report.

Here is the scripts.

Private Sub AddRow_Click()
Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = Range("B65536").End(xlUp).Row
Range("B" & LastRow & ":L" & LastRow).Copy _
Range("B" & LastRow + 1)
Application.ScreenUpdating = True
End Sub

Private Sub PrintReport_Click()
Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False
With Sheets("Report")
.Visible = -1
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A26:A58").EntireRow.Hidden = False
.Visible = 0
End With
Application.ScreenUpdating = True
End Sub



"Ron de Bruin" wrote in message
...
You have two macro's with the same name then
Delete the old one

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

I attached the script to the Print Button. I hid the Report worksheet. I
then click on the Print button and got a Compile error: Ambiguous name
detected: Hide_Print_Unhide.

Scott

"Ron de Bruin" wrote in message
...
If you hide it you must unhide it in the before you can print
Attach this macro to the button on the Main Summary page

It will unhide the sheet
hide the rows with a 0 in A
Print
unhide the rows
hide the sheet


Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False
With Sheets("Report")
.Visible = -1
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A26:A58").EntireRow.Hidden = False
.Visible = 0
End With
Application.ScreenUpdating = True
End Sub





--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

I need to execute the printing from a Print Button on my Main Summary
page. This way I can even hide the Report page so that no has to even
see it. They just complete the report from the Main Summary page and
then when finished click on the Print Report Button.

So how can that be handled in the scripting?

Frick


"Ron de Bruin" wrote in message
...
Hi Frick

We can use a event that run when you press the print button in Excel
Copy this event in the Thisworkbook module of your workbook

Then press the print button in the toolbar
Change PrintPreview to Printout in the code if it is working OK


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rw As Long
If ActiveSheet.Name = "Report" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintPreview ' for testing use .PrintPreview
.Range("A26:A58").EntireRow.Hidden = False
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

Sorry for my ignorance! I was unaware that I need to run the macro
first with Alt-F8. Now, having done that, yes the rows are removed
for prinitng.

How do I now automate it. I can tell you that I do know how to
create a button and attach the script to it. So what must be added
to the script so that when I select a created "Print Button" on my
main page it will print the report on the "Report" page with the rows
removed with 0 value.

Thank you again for all your time and patience.

Frick

"Ron de Bruin" wrote in message
...
Do you understand that you must run the macro with Alt-F8
It will not run automatic when you use the print button

This is also possible but first run the macro with Alt-F8

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

Sorry, the script that you posted below in your last response is
the script that I used. Still does not make any difference.

Frick


"Ron de Bruin" wrote in message
...
Read good

I posted this macro


Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

Using:

Cells(rw, 1).Range("A1:G1")) = 0 Then _

I pasted it and then hit the print preview button. Nothing
happens, all rows still show in the preview.
I checked the formulas in Col A and confirm that each sell either
returns a value greater then 0 or 0.

So now I am totally lost.

The formula is col A from 26 to 58 is:
IF('Main Summary'!B19=0,0,'Main Summary'!B19)

Also each cell in Col A26 through 58 has a border, but I would
not think that has any concern.

Where to now?


"Ron de Bruin" wrote in message
...
Test the macro in this thread it will test only the cells in A

I posted this

But this is not working for you because your cells are not
empty
If you want to test for 0 in the formula column A then use
this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

I want to hide those rows where the value in Col A=0 for rows
26 through 58.

In your script you have A1:G1 so I think that would mean that
ALL cells in Col's A through G would have to be empty which may
not be the case. However, if the the cell in col A =0 then
regardless of what value might be in any other column I would
want the row hidden when printing.

I tried your modified script and it still does nto hide any
rows.

Any further thoughts?

Frick

"Ron de Bruin" wrote in message
...
Hi Frick

You must use this to test the cells in A:G if they are empty

.Cells(rw, 1).Range("A1:G1")) = 0 Then _

But this is not working for you because your cells are not
empty
If you want to test for 0 in the formula column A then use
this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Hi Ron,

Thanks for the link. From the link I used the following
script:

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A26:A26")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

I wanted the macro to use just Col A for the test. I have a
formula in each row in Col A that is a IF formula that states
that If the reference cell is
0 enter the reference cell otherwise enter 0.

I set up a test range from row 26 to 58 with rows 40 through
58 =0. So, those rows should not have been included in the
printed report in range A1:J81. It did not work and all the
rows were there.

Can you explain my error.

Thanks,


"Ron de Bruin" wrote in message
...
Hi Frick

Start here
http://www.rondebruin.nl/print.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
I have a report that pulls data from another worksheet in
Range A26:J58. What I want to be able to do is assign a
button for printing the report but before printing from
A1:J70 removing or hiding any row in the A26:J58 range where
there is no data. The range is filled from row 26 down so it
is not a random fill.

Also, I want to save this workbook as a template so that it
can be used over again, so I guess it would not be good to
delete the rows in the range otherwise I would have to
recreate them.

Thanks for your help.

Removing Rows for Printing
 

Hi Frick

Remove this line in the click event

Sub Hide_Print_Unhide()




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

I have two buttons on the main worksheet. One is to add rows on the main worksheet and the other is the Print Report.

Here is the scripts.

Private Sub AddRow_Click()
Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = Range("B65536").End(xlUp).Row
Range("B" & LastRow & ":L" & LastRow).Copy _
Range("B" & LastRow + 1)
Application.ScreenUpdating = True
End Sub

Private Sub PrintReport_Click()
Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False
With Sheets("Report")
.Visible = -1
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A26:A58").EntireRow.Hidden = False
.Visible = 0
End With
Application.ScreenUpdating = True
End Sub



"Ron de Bruin" wrote in message ...
You have two macro's with the same name then
Delete the old one

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

I attached the script to the Print Button. I hid the Report worksheet. I then click on the Print button and got a Compile
error: Ambiguous name detected: Hide_Print_Unhide.

Scott

"Ron de Bruin" wrote in message ...
If you hide it you must unhide it in the before you can print
Attach this macro to the button on the Main Summary page

It will unhide the sheet
hide the rows with a 0 in A
Print
unhide the rows
hide the sheet


Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False
With Sheets("Report")
.Visible = -1
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A26:A58").EntireRow.Hidden = False
.Visible = 0
End With
Application.ScreenUpdating = True
End Sub





--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

I need to execute the printing from a Print Button on my Main Summary page. This way I can even hide the Report page so that
no has to even see it. They just complete the report from the Main Summary page and then when finished click on the Print
Report Button.

So how can that be handled in the scripting?

Frick


"Ron de Bruin" wrote in message ...
Hi Frick

We can use a event that run when you press the print button in Excel
Copy this event in the Thisworkbook module of your workbook

Then press the print button in the toolbar
Change PrintPreview to Printout in the code if it is working OK


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rw As Long
If ActiveSheet.Name = "Report" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintPreview ' for testing use .PrintPreview
.Range("A26:A58").EntireRow.Hidden = False
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

Sorry for my ignorance! I was unaware that I need to run the macro first with Alt-F8. Now, having done that, yes the rows
are removed for prinitng.

How do I now automate it. I can tell you that I do know how to create a button and attach the script to it. So what must
be added to the script so that when I select a created "Print Button" on my main page it will print the report on the
"Report" page with the rows removed with 0 value.

Thank you again for all your time and patience.

Frick

"Ron de Bruin" wrote in message ...
Do you understand that you must run the macro with Alt-F8
It will not run automatic when you use the print button

This is also possible but first run the macro with Alt-F8

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

Sorry, the script that you posted below in your last response is the script that I used. Still does not make any
difference.

Frick


"Ron de Bruin" wrote in message ...
Read good

I posted this macro


Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

Using:

Cells(rw, 1).Range("A1:G1")) = 0 Then _

I pasted it and then hit the print preview button. Nothing happens, all rows still show in the preview.
I checked the formulas in Col A and confirm that each sell either returns a value greater then 0 or 0.

So now I am totally lost.

The formula is col A from 26 to 58 is:
IF('Main Summary'!B19=0,0,'Main Summary'!B19)

Also each cell in Col A26 through 58 has a border, but I would not think that has any concern.

Where to now?


"Ron de Bruin" wrote in message ...
Test the macro in this thread it will test only the cells in A

I posted this

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

I want to hide those rows where the value in Col A=0 for rows 26 through 58.

In your script you have A1:G1 so I think that would mean that ALL cells in Col's A through G would have to be empty
which may not be the case. However, if the the cell in col A =0 then regardless of what value might be in any other
column I would want the row hidden when printing.

I tried your modified script and it still does nto hide any rows.

Any further thoughts?

Frick

"Ron de Bruin" wrote in message ...
Hi Frick

You must use this to test the cells in A:G if they are empty

.Cells(rw, 1).Range("A1:G1")) = 0 Then _

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Hi Ron,

Thanks for the link. From the link I used the following script:

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A26:A26")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

I wanted the macro to use just Col A for the test. I have a formula in each row in Col A that is a IF formula that
states that If the reference cell is
0 enter the reference cell otherwise enter 0.

I set up a test range from row 26 to 58 with rows 40 through 58 =0. So, those rows should not have been included in
the printed report in range A1:J81. It did not work and all the rows were there.

Can you explain my error.

Thanks,


"Ron de Bruin" wrote in message ...
Hi Frick

Start here
http://www.rondebruin.nl/print.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
I have a report that pulls data from another worksheet in Range A26:J58. What I want to be able to do is assign a
button for printing the report but before printing from A1:J70 removing or hiding any row in the A26:J58 range
where there is no data. The range is filled from row 26 down so it is not a random fill.

Also, I want to save this workbook as a template so that it can be used over again, so I guess it would not be
good to delete the rows in the range otherwise I would have to recreate them.

Thanks for your help.

Removing Rows for Printing
 

Ron,

Congradulations!!!!

It works like a charm. I can't tell you how much I appreciate your efforts.

I have two more challenges which I will post in a New Post now to try and
finish off this project. One is to clear a sheet of all input cells leaving
all the formula cells and the other is to link to several cells to a web
site so as too update currency rates.

Thanks again for your help and if you can assist on the other two matters
that would be great.

Frick

"Ron de Bruin" wrote in message
...
Hi Frick

Remove this line in the click event

Sub Hide_Print_Unhide()




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

I have two buttons on the main worksheet. One is to add rows on the main
worksheet and the other is the Print Report.

Here is the scripts.

Private Sub AddRow_Click()
Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = Range("B65536").End(xlUp).Row
Range("B" & LastRow & ":L" & LastRow).Copy _
Range("B" & LastRow + 1)
Application.ScreenUpdating = True
End Sub

Private Sub PrintReport_Click()
Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False
With Sheets("Report")
.Visible = -1
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A26:A58").EntireRow.Hidden = False
.Visible = 0
End With
Application.ScreenUpdating = True
End Sub



"Ron de Bruin" wrote in message
...
You have two macro's with the same name then
Delete the old one

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

I attached the script to the Print Button. I hid the Report worksheet.
I then click on the Print button and got a Compile error: Ambiguous
name detected: Hide_Print_Unhide.

Scott

"Ron de Bruin" wrote in message
...
If you hide it you must unhide it in the before you can print
Attach this macro to the button on the Main Summary page

It will unhide the sheet
hide the rows with a 0 in A
Print
unhide the rows
hide the sheet


Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False
With Sheets("Report")
.Visible = -1
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A26:A58").EntireRow.Hidden = False
.Visible = 0
End With
Application.ScreenUpdating = True
End Sub





--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

I need to execute the printing from a Print Button on my Main Summary
page. This way I can even hide the Report page so that no has to even
see it. They just complete the report from the Main Summary page and
then when finished click on the Print Report Button.

So how can that be handled in the scripting?

Frick


"Ron de Bruin" wrote in message
...
Hi Frick

We can use a event that run when you press the print button in Excel
Copy this event in the Thisworkbook module of your workbook

Then press the print button in the toolbar
Change PrintPreview to Printout in the code if it is working OK


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rw As Long
If ActiveSheet.Name = "Report" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintPreview ' for testing use .PrintPreview
.Range("A26:A58").EntireRow.Hidden = False
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

Sorry for my ignorance! I was unaware that I need to run the macro
first with Alt-F8. Now, having done that, yes the rows are removed
for prinitng.

How do I now automate it. I can tell you that I do know how to
create a button and attach the script to it. So what must be added
to the script so that when I select a created "Print Button" on my
main page it will print the report on the "Report" page with the
rows removed with 0 value.

Thank you again for all your time and patience.

Frick

"Ron de Bruin" wrote in message
...
Do you understand that you must run the macro with Alt-F8
It will not run automatic when you use the print button

This is also possible but first run the macro with Alt-F8

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

Sorry, the script that you posted below in your last response is
the script that I used. Still does not make any difference.

Frick


"Ron de Bruin" wrote in message
...
Read good

I posted this macro


Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

Using:

Cells(rw, 1).Range("A1:G1")) = 0 Then _

I pasted it and then hit the print preview button. Nothing
happens, all rows still show in the preview.
I checked the formulas in Col A and confirm that each sell
either returns a value greater then 0 or 0.

So now I am totally lost.

The formula is col A from 26 to 58 is:
IF('Main Summary'!B19=0,0,'Main Summary'!B19)

Also each cell in Col A26 through 58 has a border, but I would
not think that has any concern.

Where to now?


"Ron de Bruin" wrote in message
...
Test the macro in this thread it will test only the cells in A

I posted this

But this is not working for you because your cells are not
empty
If you want to test for 0 in the formula column A then use
this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Ron,

I want to hide those rows where the value in Col A=0 for rows
26 through 58.

In your script you have A1:G1 so I think that would mean that
ALL cells in Col's A through G would have to be empty which
may not be the case. However, if the the cell in col A =0
then regardless of what value might be in any other column I
would want the row hidden when printing.

I tried your modified script and it still does nto hide any
rows.

Any further thoughts?

Frick

"Ron de Bruin" wrote in message
...
Hi Frick

You must use this to test the cells in A:G if they are empty

.Cells(rw, 1).Range("A1:G1")) = 0 Then _

But this is not working for you because your cells are not
empty
If you want to test for 0 in the formula column A then use
this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
Hi Ron,

Thanks for the link. From the link I used the following
script:

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A26:A26")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

I wanted the macro to use just Col A for the test. I have
a formula in each row in Col A that is a IF formula that
states that If the reference cell is
0 enter the reference cell otherwise enter 0.

I set up a test range from row 26 to 58 with rows 40
through 58 =0. So, those rows should not have been included
in the printed report in range A1:J81. It did not work and
all the rows were there.

Can you explain my error.

Thanks,


"Ron de Bruin" wrote in message
...
Hi Frick

Start here
http://www.rondebruin.nl/print.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message
...
I have a report that pulls data from another worksheet in
Range A26:J58. What I want to be able to do is assign a
button for printing the report but before printing from
A1:J70 removing or hiding any row in the A26:J58 range
where there is no data. The range is filled from row 26
down so it is not a random fill.

Also, I want to save this workbook as a template so that
it can be used over again, so I guess it would not be
good to delete the rows in the range otherwise I would
have to recreate them.

Thanks for your help.

Removing Rows for Printing
 

You are welcome

Another way is to copy the macro in a normal module and enter the name of the macro in the click event

Private Sub PrintReport_Click()
Call Hide_Print_Unhide
End Sub




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

Congradulations!!!!

It works like a charm. I can't tell you how much I appreciate your efforts.

I have two more challenges which I will post in a New Post now to try and finish off this project. One is to clear a sheet of all
input cells leaving all the formula cells and the other is to link to several cells to a web site so as too update currency rates.

Thanks again for your help and if you can assist on the other two matters that would be great.

Frick

"Ron de Bruin" wrote in message ...
Hi Frick

Remove this line in the click event

Sub Hide_Print_Unhide()




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

I have two buttons on the main worksheet. One is to add rows on the main worksheet and the other is the Print Report.

Here is the scripts.

Private Sub AddRow_Click()
Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = Range("B65536").End(xlUp).Row
Range("B" & LastRow & ":L" & LastRow).Copy _
Range("B" & LastRow + 1)
Application.ScreenUpdating = True
End Sub

Private Sub PrintReport_Click()
Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False
With Sheets("Report")
.Visible = -1
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A26:A58").EntireRow.Hidden = False
.Visible = 0
End With
Application.ScreenUpdating = True
End Sub



"Ron de Bruin" wrote in message ...
You have two macro's with the same name then
Delete the old one

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

I attached the script to the Print Button. I hid the Report worksheet. I then click on the Print button and got a Compile
error: Ambiguous name detected: Hide_Print_Unhide.

Scott

"Ron de Bruin" wrote in message ...
If you hide it you must unhide it in the before you can print
Attach this macro to the button on the Main Summary page

It will unhide the sheet
hide the rows with a 0 in A
Print
unhide the rows
hide the sheet


Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False
With Sheets("Report")
.Visible = -1
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A26:A58").EntireRow.Hidden = False
.Visible = 0
End With
Application.ScreenUpdating = True
End Sub





--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

I need to execute the printing from a Print Button on my Main Summary page. This way I can even hide the Report page so that
no has to even see it. They just complete the report from the Main Summary page and then when finished click on the Print
Report Button.

So how can that be handled in the scripting?

Frick


"Ron de Bruin" wrote in message ...
Hi Frick

We can use a event that run when you press the print button in Excel
Copy this event in the Thisworkbook module of your workbook

Then press the print button in the toolbar
Change PrintPreview to Printout in the code if it is working OK


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rw As Long
If ActiveSheet.Name = "Report" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintPreview ' for testing use .PrintPreview
.Range("A26:A58").EntireRow.Hidden = False
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

Sorry for my ignorance! I was unaware that I need to run the macro first with Alt-F8. Now, having done that, yes the rows
are removed for prinitng.

How do I now automate it. I can tell you that I do know how to create a button and attach the script to it. So what must
be added to the script so that when I select a created "Print Button" on my main page it will print the report on the
"Report" page with the rows removed with 0 value.

Thank you again for all your time and patience.

Frick

"Ron de Bruin" wrote in message ...
Do you understand that you must run the macro with Alt-F8
It will not run automatic when you use the print button

This is also possible but first run the macro with Alt-F8

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

Sorry, the script that you posted below in your last response is the script that I used. Still does not make any
difference.

Frick


"Ron de Bruin" wrote in message ...
Read good

I posted this macro


Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

Using:

Cells(rw, 1).Range("A1:G1")) = 0 Then _

I pasted it and then hit the print preview button. Nothing happens, all rows still show in the preview.
I checked the formulas in Col A and confirm that each sell either returns a value greater then 0 or 0.

So now I am totally lost.

The formula is col A from 26 to 58 is:
IF('Main Summary'!B19=0,0,'Main Summary'!B19)

Also each cell in Col A26 through 58 has a border, but I would not think that has any concern.

Where to now?


"Ron de Bruin" wrote in message ...
Test the macro in this thread it will test only the cells in A

I posted this

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Ron,

I want to hide those rows where the value in Col A=0 for rows 26 through 58.

In your script you have A1:G1 so I think that would mean that ALL cells in Col's A through G would have to be empty
which may not be the case. However, if the the cell in col A =0 then regardless of what value might be in any other
column I would want the row hidden when printing.

I tried your modified script and it still does nto hide any rows.

Any further thoughts?

Frick

"Ron de Bruin" wrote in message ...
Hi Frick

You must use this to test the cells in A:G if they are empty

.Cells(rw, 1).Range("A1:G1")) = 0 Then _

But this is not working for you because your cells are not empty
If you want to test for 0 in the formula column A then use this

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
Hi Ron,

Thanks for the link. From the link I used the following script:

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Report")
For rw = 26 To 58
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A26:A26")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

I wanted the macro to use just Col A for the test. I have a formula in each row in Col A that is a IF formula
that states that If the reference cell is
0 enter the reference cell otherwise enter 0.

I set up a test range from row 26 to 58 with rows 40 through 58 =0. So, those rows should not have been included
in the printed report in range A1:J81. It did not work and all the rows were there.

Can you explain my error.

Thanks,


"Ron de Bruin" wrote in message ...
Hi Frick

Start here
http://www.rondebruin.nl/print.htm

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Frick" wrote in message ...
I have a report that pulls data from another worksheet in Range A26:J58. What I want to be able to do is assign a
button for printing the report but before printing from A1:J70 removing or hiding any row in the A26:J58 range
where there is no data. The range is filled from row 26 down so it is not a random fill.

Also, I want to save this workbook as a template so that it can be used over again, so I guess it would not be
good to delete the rows in the range otherwise I would have to recreate them.

Thanks for your help.