Re: Removing Rows for Printing

Ron,

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

So how can that be handled in the scripting?

Frick


"Ron de Bruin" <rondebruin@xxxxxxxxxxxx> wrote in message
news:OjX7irIRGHA.5296@xxxxxxxxxxxxxxxxxxxxxxx
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 Active***.Name = "Report" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With Active***
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" <sfrickman@xxxxxxx> wrote in message
news:V6mQf.539060$qk4.228918@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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" <rondebruin@xxxxxxxxxxxx> wrote in message
news:uXPhtVIRGHA.5552@xxxxxxxxxxxxxxxxxxxxxxx
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" <sfrickman@xxxxxxx> wrote in message
news:KElQf.538926$qk4.271519@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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" <rondebruin@xxxxxxxxxxxx> wrote in message
news:ubaukLIRGHA.5552@xxxxxxxxxxxxxxxxxxxxxxx
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" <sfrickman@xxxxxxx> wrote in message
news:ablQf.7762$8w2.4765@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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" <rondebruin@xxxxxxxxxxxx> wrote in message
news:%23va69xHRGHA.5500@xxxxxxxxxxxxxxxxxxxxxxx
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" <sfrickman@xxxxxxx> wrote in message
news:hNkQf.7703$8w2.942@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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" <rondebruin@xxxxxxxxxxxx> wrote in message
news:%23eDW$aHRGHA.4344@xxxxxxxxxxxxxxxxxxxxxxx
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" <sfrickman@xxxxxxx> wrote in message
news:e2kQf.7593$8w2.4494@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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" <rondebruin@xxxxxxxxxxxx> wrote in message
news:evzwpHHRGHA.4956@xxxxxxxxxxxxxxxxxxxxxxx
Hi Frick

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

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


"Frick" <sfrickman@xxxxxxx> wrote in message
news:aCjQf.7528$8w2.5209@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I have a report that pulls data from another work*** 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.


























.

Loading