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.

Hi Frick

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

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


"Frick" <[email protected]> wrote in message news:[email protected]...
>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" <[email protected]> wrote in message
news:[email protected]...
> Hi Frick
>
> Start here
> http://www.rondebruin.nl/print.htm
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Frick" <[email protected]> wrote in message
> news:[email protected]...
>>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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> Hi Frick
>>
>> Start here
>> http://www.rondebruin.nl/print.htm
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "Frick" <[email protected]> wrote in message news:[email protected]...
>>>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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> Hi Frick
>>>
>>> Start here
>>> http://www.rondebruin.nl/print.htm
>>>
>>> --
>>> Regards Ron de Bruin
>>> http://www.rondebruin.nl
>>>
>>>
>>> "Frick" <[email protected]> wrote in message
>>> news:[email protected]...
>>>>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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:%[email protected]...
>> 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" <[email protected]> wrote in message news:[email protected]...
>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>> Hi Frick
>>>>
>>>> Start here
>>>> http://www.rondebruin.nl/print.htm
>>>>
>>>> --
>>>> Regards Ron de Bruin
>>>> http://www.rondebruin.nl
>>>>
>>>>
>>>> "Frick" <[email protected]> wrote in message news:[email protected]...
>>>>>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,

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>> Hi Frick
>>>>>
>>>>> Start here
>>>>> http://www.rondebruin.nl/print.htm
>>>>>
>>>>> --
>>>>> Regards Ron de Bruin
>>>>> http://www.rondebruin.nl
>>>>>
>>>>>
>>>>> "Frick" <[email protected]> wrote in message
>>>>> news:[email protected]...
>>>>>>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.
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:%[email protected]...
>> 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" <[email protected]> wrote in message news:[email protected]...
>>> 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" <[email protected]> wrote in message news:%[email protected]...
>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>> Hi Frick
>>>>>>
>>>>>> Start here
>>>>>> http://www.rondebruin.nl/print.htm
>>>>>>
>>>>>> --
>>>>>> Regards Ron de Bruin
>>>>>> http://www.rondebruin.nl
>>>>>>
>>>>>>
>>>>>> "Frick" <[email protected]> wrote in message news:[email protected]...
>>>>>>>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,

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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" <[email protected]> wrote in message
>>>> news:%[email protected]...
>>>>> 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" <[email protected]> wrote in message
>>>>> news:[email protected]...
>>>>>> 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" <[email protected]> wrote in message
>>>>>> news:[email protected]...
>>>>>>> Hi Frick
>>>>>>>
>>>>>>> Start here
>>>>>>> http://www.rondebruin.nl/print.htm
>>>>>>>
>>>>>>> --
>>>>>>> Regards Ron de Bruin
>>>>>>> http://www.rondebruin.nl
>>>>>>>
>>>>>>>
>>>>>>> "Frick" <[email protected]> wrote in message
>>>>>>> news:[email protected]...
>>>>>>>>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.
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

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

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> 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" <[email protected]> wrote in message news:[email protected]...
>>> 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" <[email protected]> wrote in message news:%[email protected]...
>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>> 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" <[email protected]> wrote in message news:%[email protected]...
>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>> Hi Frick
>>>>>>>>
>>>>>>>> Start here
>>>>>>>> http://www.rondebruin.nl/print.htm
>>>>>>>>
>>>>>>>> --
>>>>>>>> Regards Ron de Bruin
>>>>>>>> http://www.rondebruin.nl
>>>>>>>>
>>>>>>>>
>>>>>>>> "Frick" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>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,

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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" <[email protected]> wrote in message
>>>> news:%[email protected]...
>>>>> 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" <[email protected]> wrote in message
>>>>> news:[email protected]...
>>>>>> 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" <[email protected]> wrote in message
>>>>>> news:%[email protected]...
>>>>>>> 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" <[email protected]> wrote in message
>>>>>>> news:[email protected]...
>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>> news:[email protected]...
>>>>>>>>> Hi Frick
>>>>>>>>>
>>>>>>>>> Start here
>>>>>>>>> http://www.rondebruin.nl/print.htm
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Regards Ron de Bruin
>>>>>>>>> http://www.rondebruin.nl
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> "Frick" <[email protected]> wrote in message
>>>>>>>>> news:[email protected]...
>>>>>>>>>>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

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> 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" <[email protected]> wrote in message news:[email protected]...
>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>> 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" <[email protected]> wrote in message news:%[email protected]...
>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>> 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" <[email protected]> wrote in message news:%[email protected]...
>>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>> Hi Frick
>>>>>>>>>>
>>>>>>>>>> Start here
>>>>>>>>>> http://www.rondebruin.nl/print.htm
>>>>>>>>>>
>>>>>>>>>> --
>>>>>>>>>> Regards Ron de Bruin
>>>>>>>>>> http://www.rondebruin.nl
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> "Frick" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>>>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 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>> 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" <[email protected]> wrote in message
>>>>> news:[email protected]...
>>>>>> 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" <[email protected]> wrote in message
>>>>>> news:%[email protected]...
>>>>>>> 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" <[email protected]> wrote in message
>>>>>>> news:[email protected]...
>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>> news:%[email protected]...
>>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>>> news:[email protected]...
>>>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>>>> news:[email protected]...
>>>>>>>>>>> Hi Frick
>>>>>>>>>>>
>>>>>>>>>>> Start here
>>>>>>>>>>> http://www.rondebruin.nl/print.htm
>>>>>>>>>>>
>>>>>>>>>>> --
>>>>>>>>>>> Regards Ron de Bruin
>>>>>>>>>>> http://www.rondebruin.nl
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> "Frick" <[email protected]> wrote in message
>>>>>>>>>>> news:[email protected]...
>>>>>>>>>>>>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.
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> 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" <[email protected]> wrote in message news:[email protected]...
>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>> 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" <[email protected]> wrote in message news:%[email protected]...
>>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>> 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" <[email protected]> wrote in message news:%[email protected]...
>>>>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>>>> Hi Frick
>>>>>>>>>>>>
>>>>>>>>>>>> Start here
>>>>>>>>>>>> http://www.rondebruin.nl/print.htm
>>>>>>>>>>>>
>>>>>>>>>>>> --
>>>>>>>>>>>> Regards Ron de Bruin
>>>>>>>>>>>> http://www.rondebruin.nl
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>> "Frick" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>>>>>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 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>> 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" <[email protected]> wrote in message
>>>>> news:[email protected]...
>>>>>> 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" <[email protected]> wrote in message
>>>>>> news:[email protected]...
>>>>>>> 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" <[email protected]> wrote in message
>>>>>>> news:[email protected]...
>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>> news:%[email protected]...
>>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>>> news:[email protected]...
>>>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>>>> news:%[email protected]...
>>>>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>>>>> news:[email protected]...
>>>>>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>>>>>> news:[email protected]...
>>>>>>>>>>>>> Hi Frick
>>>>>>>>>>>>>
>>>>>>>>>>>>> Start here
>>>>>>>>>>>>> http://www.rondebruin.nl/print.htm
>>>>>>>>>>>>>
>>>>>>>>>>>>> --
>>>>>>>>>>>>> Regards Ron de Bruin
>>>>>>>>>>>>> http://www.rondebruin.nl
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> "Frick" <[email protected]> wrote in message
>>>>>>>>>>>>> news:[email protected]...
>>>>>>>>>>>>>>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.
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

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

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


"Frick" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> 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" <[email protected]> wrote in message news:[email protected]...
>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>> 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" <[email protected]> wrote in message news:%[email protected]...
>>>>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>>> 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" <[email protected]> wrote in message news:%[email protected]...
>>>>>>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>>>>>> Hi Frick
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> Start here
>>>>>>>>>>>>>> http://www.rondebruin.nl/print.htm
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> --
>>>>>>>>>>>>>> Regards Ron de Bruin
>>>>>>>>>>>>>> http://www.rondebruin.nl
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> "Frick" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>>>>>>>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 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" <[email protected]> wrote in message
news:[email protected]...
> You have two macro's with the same name then
> Delete the old one
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Frick" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>> 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" <[email protected]> wrote in message
>>>>> news:[email protected]...
>>>>>> 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" <[email protected]> wrote in message
>>>>>> news:[email protected]...
>>>>>>> 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" <[email protected]> wrote in message
>>>>>>> news:[email protected]...
>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>> news:[email protected]...
>>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>>> news:[email protected]...
>>>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>>>> news:%[email protected]...
>>>>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>>>>> news:[email protected]...
>>>>>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>>>>>> news:%[email protected]...
>>>>>>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>>>>>>> news:[email protected]...
>>>>>>>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>>>>>>>> news:[email protected]...
>>>>>>>>>>>>>>> Hi Frick
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> Start here
>>>>>>>>>>>>>>> http://www.rondebruin.nl/print.htm
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>> Regards Ron de Bruin
>>>>>>>>>>>>>>> http://www.rondebruin.nl
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> "Frick" <[email protected]> wrote in message
>>>>>>>>>>>>>>> news:[email protected]...
>>>>>>>>>>>>>>>>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

Remove this line in the click event

Sub Hide_Print_Unhide()




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


"Frick" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> You have two macro's with the same name then
>> Delete the old one
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "Frick" <[email protected]> wrote in message news:[email protected]...
>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>>> 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" <[email protected]> wrote in message news:%[email protected]...
>>>>>>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>>>>> 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" <[email protected]> wrote in message news:%[email protected]...
>>>>>>>>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>>>>>>>> Hi Frick
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> Start here
>>>>>>>>>>>>>>>> http://www.rondebruin.nl/print.htm
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>> Regards Ron de Bruin
>>>>>>>>>>>>>>>> http://www.rondebruin.nl
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> "Frick" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>>>>>>>>>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,

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" <[email protected]> wrote in message
news:%[email protected]...
> Hi Frick
>
> Remove this line in the click event
>
> Sub Hide_Print_Unhide()
>
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Frick" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> You have two macro's with the same name then
>>> Delete the old one
>>>
>>> --
>>> Regards Ron de Bruin
>>> http://www.rondebruin.nl
>>>
>>>
>>> "Frick" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>> 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" <[email protected]> wrote in message
>>>>> news:[email protected]...
>>>>>> 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" <[email protected]> wrote in message
>>>>>> news:[email protected]...
>>>>>>> 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" <[email protected]> wrote in message
>>>>>>> news:[email protected]...
>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>> news:[email protected]...
>>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>>> news:[email protected]...
>>>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>>>> news:[email protected]...
>>>>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>>>>> news:[email protected]...
>>>>>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>>>>>> news:%[email protected]...
>>>>>>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>>>>>>> news:[email protected]...
>>>>>>>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>>>>>>>> news:%[email protected]...
>>>>>>>>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>>>>>>>>> news:[email protected]...
>>>>>>>>>>>>>>>> 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" <[email protected]> wrote in message
>>>>>>>>>>>>>>>> news:[email protected]...
>>>>>>>>>>>>>>>>> Hi Frick
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> Start here
>>>>>>>>>>>>>>>>> http://www.rondebruin.nl/print.htm
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>>> Regards Ron de Bruin
>>>>>>>>>>>>>>>>> http://www.rondebruin.nl
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> "Frick" <[email protected]> wrote in message
>>>>>>>>>>>>>>>>> news:[email protected]...
>>>>>>>>>>>>>>>>>>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.
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:%[email protected]...
>> Hi Frick
>>
>> Remove this line in the click event
>>
>> Sub Hide_Print_Unhide()
>>
>>
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "Frick" <[email protected]> wrote in message news:[email protected]...
>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>> You have two macro's with the same name then
>>>> Delete the old one
>>>>
>>>> --
>>>> Regards Ron de Bruin
>>>> http://www.rondebruin.nl
>>>>
>>>>
>>>> "Frick" <[email protected]> wrote in message news:[email protected]...
>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>>>>> 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" <[email protected]> wrote in message news:%[email protected]...
>>>>>>>>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>>>>>>> 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" <[email protected]> wrote in message news:%[email protected]...
>>>>>>>>>>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>>>>>>>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>>>>>>>>>> Hi Frick
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> Start here
>>>>>>>>>>>>>>>>>> http://www.rondebruin.nl/print.htm
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>>>> Regards Ron de Bruin
>>>>>>>>>>>>>>>>>> http://www.rondebruin.nl
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> "Frick" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>>>>>>>>>>>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.
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>