Set Print Area
 

Hi All-

I am successfully using the below macro to define print area. The
number of rows remains constant but the columns vary depending on the
number of tests a person performs. The macro therefore will select and
print the colums that are headed in row A. The row A headers are
linked to other cells in the workbook that define a date.

Range("A1:Z51").Select
ActiveSheet.PageSetup.PrintArea = "A1:OFFSET(A1,50,COUNTA
(A1:Z1)-1)"

this however relies on the cells in row A that are not wanted to be
printed to be blank. I now want to fully automate the wookbook and the
problem I have is now the cells in row A are linked to another cell
(T4 in this instance) that calculates a date, so there is always text
in the row A column headers. I have tried to use the following =IF
(T2,T4,"") (where T2 is date of birth and T4 is calculated age) to
make the cells in Row A appear blank if there is not date of birth
present. However teh COUNTA function obviously works on cells being
totally blank (i.e. not containing formulas) so no longer serves the
purpose.


Has anyone got any ideas on how to alter the PrintArea macro to set
based on whether a cell is displayign a value?

Thanks (as always) for any help offered

Best Wishes

Paul

p.s. apologies if this question appears twice. I thought I posted it
before but can't find it anywhere!!!!

Set Print Area
 

1. There is no such thing as row A. Do you mean col A or Row 1?
2. You do not have to use a macro to define the range name. In fact, it only
slows things down.
Use a defined name by insertnamedefine and modify your Print_Area name to

=OFFSET($A$1,0,0,MATCH(9999999,$A:$A),COUNTA($1:$1 ))

which assumes row 1 has data in all cells and column A has numbers and may
have blanks.
Now the print_area will be self adjusting without a macro.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Porky79" wrote in message
...
Hi All-

I am successfully using the below macro to define print area. The
number of rows remains constant but the columns vary depending on the
number of tests a person performs. The macro therefore will select and
print the colums that are headed in row A. The row A headers are
linked to other cells in the workbook that define a date.

Range("A1:Z51").Select
ActiveSheet.PageSetup.PrintArea = "A1:OFFSET(A1,50,COUNTA
(A1:Z1)-1)"

this however relies on the cells in row A that are not wanted to be
printed to be blank. I now want to fully automate the wookbook and the
problem I have is now the cells in row A are linked to another cell
(T4 in this instance) that calculates a date, so there is always text
in the row A column headers. I have tried to use the following =IF
(T2,T4,"") (where T2 is date of birth and T4 is calculated age) to
make the cells in Row A appear blank if there is not date of birth
present. However teh COUNTA function obviously works on cells being
totally blank (i.e. not containing formulas) so no longer serves the
purpose.


Has anyone got any ideas on how to alter the PrintArea macro to set
based on whether a cell is displayign a value?

Thanks (as always) for any help offered

Best Wishes

Paul

p.s. apologies if this question appears twice. I thought I posted it
before but can't find it anywhere!!!!