Ron,
Congradulations!!!!
It works like a charm. I can't tell you how much I appreciate your efforts.
I have two more challenges which I will post in a New Post now to try and
finish off this project. One is to clear a sheet of all input cells leaving
all the formula cells and the other is to link to several cells to a web
site so as too update currency rates.
Thanks again for your help and if you can assist on the other two matters
that would be great.
Frick
"Ron de Bruin" wrote in message
...
Hi Frick
Remove this line in the click event
Sub Hide_Print_Unhide()
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Frick" wrote in message
...
Ron,
I have two buttons on the main worksheet. One is to add rows on the main
worksheet and the other is the Print Report.
Here is the scripts.
Private Sub AddRow_Click()
Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = Range("B65536").End(xlUp).Row
Range("B" & LastRow & ":L" & LastRow).Copy _
Range("B" & LastRow + 1)
Application.ScreenUpdating = True
End Sub
Private Sub PrintReport_Click()
Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False
With Sheets("Report")
.Visible = -1
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A26:A58").EntireRow.Hidden = False
.Visible = 0
End With
Application.ScreenUpdating = True
End Sub
"Ron de Bruin" wrote in message
...
You have two macro's with the same name then
Delete the old one
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Frick" wrote in message
...
Ron,
I attached the script to the Print Button. I hid the Report worksheet.
I then click on the Print button and got a Compile error: Ambiguous
name detected: Hide_Print_Unhide.
Scott
"Ron de Bruin" wrote in message
...
If you hide it you must unhide it in the before you can print
Attach this macro to the button on the Main Summary page
It will unhide the sheet
hide the rows with a 0 in A
Print
unhide the rows
hide the sheet
Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False
With Sheets("Report")
.Visible = -1
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A26:A58").EntireRow.Hidden = False
.Visible = 0
End With
Application.ScreenUpdating = True
End Sub
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Frick" wrote in message
...
Ron,
I need to execute the printing from a Print Button on my Main Summary
page. This way I can even hide the Report page so that no has to even
see it. They just complete the report from the Main Summary page and
then when finished click on the Print Report Button.
So how can that be handled in the scripting?
Frick
"Ron de Bruin" wrote in message
...
Hi Frick
We can use a event that run when you press the print button in Excel
Copy this event in the Thisworkbook module of your workbook
Then press the print button in the toolbar
Change PrintPreview to Printout in the code if it is working OK
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rw As Long
If ActiveSheet.Name = "Report" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintPreview ' for testing use .PrintPreview
.Range("A26:A58").EntireRow.Hidden = False
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Frick" wrote in message
...
Ron,
Sorry for my ignorance! I was unaware that I need to run the macro
first with Alt-F8. Now, having done that, yes the rows are removed
for prinitng.
How do I now automate it. I can tell you that I do know how to
create a button and attach the script to it. So what must be added
to the script so that when I select a created "Print Button" on my
main page it will print the report on the "Report" page with the
rows removed with 0 value.
Thank you again for all your time and patience.
Frick
"Ron de Bruin" wrote in message
...
Do you understand that you must run the macro with Alt-F8
It will not run automatic when you use the print button
This is also possible but first run the macro with Alt-F8
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Frick" wrote in message
...
Ron,
Sorry, the script that you posted below in your last response is
the script that I used. Still does not make any difference.
Frick
"Ron de Bruin" wrote in message
...
Read good
I posted this macro
Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False
With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With
Application.ScreenUpdating = True
End Sub
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Frick" wrote in message
...
Ron,
Using:
Cells(rw, 1).Range("A1:G1")) = 0 Then _
I pasted it and then hit the print preview button. Nothing
happens, all rows still show in the preview.
I checked the formulas in Col A and confirm that each sell
either returns a value greater then 0 or 0.
So now I am totally lost.
The formula is col A from 26 to 58 is:
IF('Main Summary'!B19=0,0,'Main Summary'!B19)
Also each cell in Col A26 through 58 has a border, but I would
not think that has any concern.
Where to now?
"Ron de Bruin" wrote in message
...
Test the macro in this thread it will test only the cells in A
I posted this
But this is not working for you because your cells are not
empty
If you want to test for 0 in the formula column A then use
this
Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False
With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With
Application.ScreenUpdating = True
End Sub
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Frick" wrote in message
...
Ron,
I want to hide those rows where the value in Col A=0 for rows
26 through 58.
In your script you have A1:G1 so I think that would mean that
ALL cells in Col's A through G would have to be empty which
may not be the case. However, if the the cell in col A =0
then regardless of what value might be in any other column I
would want the row hidden when printing.
I tried your modified script and it still does nto hide any
rows.
Any further thoughts?
Frick
"Ron de Bruin" wrote in message
...
Hi Frick
You must use this to test the cells in A:G if they are empty
.Cells(rw, 1).Range("A1:G1")) = 0 Then _
But this is not working for you because your cells are not
empty
If you want to test for 0 in the formula column A then use
this
Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False
With Sheets("Report")
For rw = 26 To 58
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With
Application.ScreenUpdating = True
End Sub
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Frick" wrote in message
...
Hi Ron,
Thanks for the link. From the link I used the following
script:
Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False
With Sheets("Report")
For rw = 26 To 58
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A26:A26")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A1:J81").EntireRow.Hidden = False
End With
Application.ScreenUpdating = True
End Sub
I wanted the macro to use just Col A for the test. I have
a formula in each row in Col A that is a IF formula that
states that If the reference cell is
0 enter the reference cell otherwise enter 0.
I set up a test range from row 26 to 58 with rows 40
through 58 =0. So, those rows should not have been included
in the printed report in range A1:J81. It did not work and
all the rows were there.
Can you explain my error.
Thanks,
"Ron de Bruin" wrote in message
...
Hi Frick
Start here
http://www.rondebruin.nl/print.htm
--
Regards Ron de Bruin
http://www.rondebruin.nl
"Frick" wrote in message
...
I have a report that pulls data from another worksheet in
Range A26:J58. What I want to be able to do is assign a
button for printing the report but before printing from
A1:J70 removing or hiding any row in the A26:J58 range
where there is no data. The range is filled from row 26
down so it is not a random fill.
Also, I want to save this workbook as a template so that
it can be used over again, so I guess it would not be
good to delete the rows in the range otherwise I would
have to recreate them.
Thanks for your help.