Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
PrintArea Macro Help
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 columns 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 the 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!!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
PrintArea Macro Help
Since you're using a macro, I would find the last used row in column A and then
just loop up looking to see if the cell looked blank. Dim LastRow as long dim iRow as long with activesheet lastrow = .cells(.rows.count,"A").end(xlup).row for irow = lastrow to 1 step -1 if .cells(irow,"A").value = "" then 'keep looking else exit for end if next irow .pagesetup.printarea = "A1:Z" & lastrow end with ============= But there are other ways to do this, too. If you want a non-macro approach... Saved from a previous post: If those formulas appear at the end of the data and you don't want to use autofilter... If I can pick out a column indicates if that row is used or not, then I like this technique: (I used column A in my sample, but you can use any column you want.) Insert|Name|Define Names in workbook: Sheet1!LastRow Use this formula Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000<""),ROW(Sheet1!$A$1:$A$1000) ) (Make that 1000 big enough to extend past the last possible row.) Then once mo Insert|Name|Define Names in workbook: Sheet1!Print_Area Use this formula Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3) That last 3 represents the last column to print (A:C in my example). And change the worksheet (sheet1) if necessary (in all the places). If you go into file|page setup, you may find that the print range is changed to a specific range. And you'll have to reapply the Print_Area name. Porky79 wrote: 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 columns 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 the 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!!!! -- Dave Peterson |