Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Print Visible Data Only
Hi,
I am currently producing a worksheet which presents the number of outstanding errors that are produced by a group of around 200 people. The data is in a pivot table, but this is hidden as the layout is too rigid in a pivot table. From the drop down I can select any area of the England which is linked to every person in the dataset, so as a result the number of rows shown can vary from area to area. So conditional formating and formulas have created on every cell even 300 or so blank ones, in case new people are added to the dataset. The formula is present as it either selects the data from the pivot table, or shows a blank if there is no data. The conditional formatting is present as there is a traffic light system depending on the number of errors by each person in the dataset. In addition to this there formatting to the cells showing change so that the '+' sign appears when a change in the total from a previous period is upward, and a '-' sign appears when the change is downward and finally a blank before the zero if there is no change. So, my problem is that I wish to only print the visible area every time the area chosen is changed via a macro, as people other than myself (and these people may not be so computer literate) will be using this and I would like this feature for their convienence. But due to the fact that I have put formulas in the blank rows below, a simple 'set print area' with 'shift', 'ctrl' and 'end' to select the print area won't work. I hope I have explained my situation throughly enough and I hope you can help me on this. Many Thanks, Mr Patel |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Print Visible Data Only
Mr Patel,
If your formulas return a value of "" when the row is not used, you could use a macro, along the lines of Sub HideRows() Dim myCell As Range Range("A1:A500").EntireRow.Hidden = False For Each myCell In Range("A1:A500") If myCell.Value = "" Then myCell.EntireRow.Hidden = True End If Next myCell End Sub HTH, Bernie MS Excel MVP "Mitesh" wrote in message ... Hi, I am currently producing a worksheet which presents the number of outstanding errors that are produced by a group of around 200 people. The data is in a pivot table, but this is hidden as the layout is too rigid in a pivot table. From the drop down I can select any area of the England which is linked to every person in the dataset, so as a result the number of rows shown can vary from area to area. So conditional formating and formulas have created on every cell even 300 or so blank ones, in case new people are added to the dataset. The formula is present as it either selects the data from the pivot table, or shows a blank if there is no data. The conditional formatting is present as there is a traffic light system depending on the number of errors by each person in the dataset. In addition to this there formatting to the cells showing change so that the '+' sign appears when a change in the total from a previous period is upward, and a '-' sign appears when the change is downward and finally a blank before the zero if there is no change. So, my problem is that I wish to only print the visible area every time the area chosen is changed via a macro, as people other than myself (and these people may not be so computer literate) will be using this and I would like this feature for their convienence. But due to the fact that I have put formulas in the blank rows below, a simple 'set print area' with 'shift', 'ctrl' and 'end' to select the print area won't work. I hope I have explained my situation throughly enough and I hope you can help me on this. Many Thanks, Mr Patel |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Print Visible Data Only
Mr Deitrick,
Apologies for the misconduct, your advice worked a treat, but it appears i'm having a problem to select and set a print area (that covers the visible data only) that could apply for any number of rows. Any suggestions would be gratefully received. Regards, Mr Patel "Bernie Deitrick" wrote: Mr Patel, If your formulas return a value of "" when the row is not used, you could use a macro, along the lines of Sub HideRows() Dim myCell As Range Range("A1:A500").EntireRow.Hidden = False For Each myCell In Range("A1:A500") If myCell.Value = "" Then myCell.EntireRow.Hidden = True End If Next myCell End Sub HTH, Bernie MS Excel MVP "Mitesh" wrote in message ... Hi, I am currently producing a worksheet which presents the number of outstanding errors that are produced by a group of around 200 people. The data is in a pivot table, but this is hidden as the layout is too rigid in a pivot table. From the drop down I can select any area of the England which is linked to every person in the dataset, so as a result the number of rows shown can vary from area to area. So conditional formating and formulas have created on every cell even 300 or so blank ones, in case new people are added to the dataset. The formula is present as it either selects the data from the pivot table, or shows a blank if there is no data. The conditional formatting is present as there is a traffic light system depending on the number of errors by each person in the dataset. In addition to this there formatting to the cells showing change so that the '+' sign appears when a change in the total from a previous period is upward, and a '-' sign appears when the change is downward and finally a blank before the zero if there is no change. So, my problem is that I wish to only print the visible area every time the area chosen is changed via a macro, as people other than myself (and these people may not be so computer literate) will be using this and I would like this feature for their convienence. But due to the fact that I have put formulas in the blank rows below, a simple 'set print area' with 'shift', 'ctrl' and 'end' to select the print area won't work. I hope I have explained my situation throughly enough and I hope you can help me on this. Many Thanks, Mr Patel |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Print Visible Data Only
Hi again,
Ignore the below, i've just figure out how to do this myself. Many thanks for your help, it is very much appreciated. Thanks, Mitesh "Mitesh" wrote: Mr Deitrick, Apologies for the misconduct, your advice worked a treat, but it appears i'm having a problem to select and set a print area (that covers the visible data only) that could apply for any number of rows. Any suggestions would be gratefully received. Regards, Mr Patel "Bernie Deitrick" wrote: Mr Patel, If your formulas return a value of "" when the row is not used, you could use a macro, along the lines of Sub HideRows() Dim myCell As Range Range("A1:A500").EntireRow.Hidden = False For Each myCell In Range("A1:A500") If myCell.Value = "" Then myCell.EntireRow.Hidden = True End If Next myCell End Sub HTH, Bernie MS Excel MVP "Mitesh" wrote in message ... Hi, I am currently producing a worksheet which presents the number of outstanding errors that are produced by a group of around 200 people. The data is in a pivot table, but this is hidden as the layout is too rigid in a pivot table. From the drop down I can select any area of the England which is linked to every person in the dataset, so as a result the number of rows shown can vary from area to area. So conditional formating and formulas have created on every cell even 300 or so blank ones, in case new people are added to the dataset. The formula is present as it either selects the data from the pivot table, or shows a blank if there is no data. The conditional formatting is present as there is a traffic light system depending on the number of errors by each person in the dataset. In addition to this there formatting to the cells showing change so that the '+' sign appears when a change in the total from a previous period is upward, and a '-' sign appears when the change is downward and finally a blank before the zero if there is no change. So, my problem is that I wish to only print the visible area every time the area chosen is changed via a macro, as people other than myself (and these people may not be so computer literate) will be using this and I would like this feature for their convienence. But due to the fact that I have put formulas in the blank rows below, a simple 'set print area' with 'shift', 'ctrl' and 'end' to select the print area won't work. I hope I have explained my situation throughly enough and I hope you can help me on this. Many Thanks, Mr Patel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
print visible data only | Excel Worksheet Functions | |||
How can I print a chart with visible grid lines? | Excel Discussion (Misc queries) | |||
My spreadsheet is only visible in print preview. | Excel Discussion (Misc queries) | |||
Autoshapes not visible on spreadsheet but visible in print preview | Excel Discussion (Misc queries) | |||
Can I place visible text in cell that won't print | Excel Discussion (Misc queries) |