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.

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.

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.

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.

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.

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.