Removing Rows for Printing

F

Frick

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.
 
F

Frick

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.
Click to expand...

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,
 
R

Ron de Bruin

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
 
F

Frick

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
 
F

Frick

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?
 
R

Ron de Bruin

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
 
F

Frick

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
 
P

Pete_UK

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
 
R

Ron de Bruin

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
 
F

Frick

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
 
R

Ron de Bruin

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
 
F

Frick

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
 
R

Ron de Bruin

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
 
F

Frick

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
 
F

Frick

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
 
F

Frick

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
 
Top