Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic print ranges ...
hi ,
I need to be able to set a print range according to the last column with data. The last used column varies with different entitities. The last row on the print range should always be 710. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic print ranges ...
Surely, if you set it to al columns, the blank columns are just ignored?
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "manfareed" wrote in message ... hi , I need to be able to set a print range according to the last column with data. The last used column varies with different entitities. The last row on the print range should always be 710. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic print ranges ...
You can define a dynamic range for the print area. First define your print
area the usual way (File menu). Then go to Insert menu Names Define, and select Print_Area. Enter a formula like this in the Refers To box (replacing the hard-coded cell address): =OFFSET(Sheet1!$A$1,0,0,710,COUNT(Sheet1!$2:$2)) The print area will be as wide as you have elements in row 2, so if there are no blank cells in row 2, it will give the result you want, and ignore headers in row 1 that extend further than the data. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "manfareed" wrote in message ... hi , I need to be able to set a print range according to the last column with data. The last used column varies with different entitities. The last row on the print range should always be 710. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic print ranges ...
Don't they still print out? I interpreted the question as having a data
table with a lot of columns with headers, but only with some columns populated. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Bob Phillips" wrote in message ... Surely, if you set it to al columns, the blank columns are just ignored? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "manfareed" wrote in message ... hi , I need to be able to set a print range according to the last column with data. The last used column varies with different entitities. The last row on the print range should always be 710. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic print ranges ...
I see the thinking. I read it as nothing at all, not even headers.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jon Peltier" wrote in message ... Don't they still print out? I interpreted the question as having a data table with a lot of columns with headers, but only with some columns populated. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Bob Phillips" wrote in message ... Surely, if you set it to al columns, the blank columns are just ignored? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "manfareed" wrote in message ... hi , I need to be able to set a print range according to the last column with data. The last used column varies with different entitities. The last row on the print range should always be 710. Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic print ranges ...
I think the question has been misinterpreted. This is to do with our
financial reporting tool. A P&L is ran for each country but they all differ because they have different number of branches. For Example for France the last column would be at "AX" but if I ran the report for say the UK the last column would be "BM". I need a macro that sets the print range accordingly. Thanks "Jon Peltier" wrote: You can define a dynamic range for the print area. First define your print area the usual way (File menu). Then go to Insert menu Names Define, and select Print_Area. Enter a formula like this in the Refers To box (replacing the hard-coded cell address): =OFFSET(Sheet1!$A$1,0,0,710,COUNT(Sheet1!$2:$2)) The print area will be as wide as you have elements in row 2, so if there are no blank cells in row 2, it will give the result you want, and ignore headers in row 1 that extend further than the data. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "manfareed" wrote in message ... hi , I need to be able to set a print range according to the last column with data. The last used column varies with different entitities. The last row on the print range should always be 710. Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic print ranges ...
My suggestion still works, or you could assign the print area to the used
range of the worksheet: With ActiveSheet .UsedRange.Name = "'" & .Name & "'!Print_Area" End With then proceed to print. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "manfareed" wrote in message ... I think the question has been misinterpreted. This is to do with our financial reporting tool. A P&L is ran for each country but they all differ because they have different number of branches. For Example for France the last column would be at "AX" but if I ran the report for say the UK the last column would be "BM". I need a macro that sets the print range accordingly. Thanks "Jon Peltier" wrote: You can define a dynamic range for the print area. First define your area the usual way (File menu). Then go to Insert menu Names Define, and select Print_Area. Enter a formula like this in the Refers To box (replacing the hard-coded cell address): =OFFSET(Sheet1!$A$1,0,0,710,COUNT(Sheet1!$2:$2)) The print area will be as wide as you have elements in row 2, so if there are no blank cells in row 2, it will give the result you want, and ignore headers in row 1 that extend further than the data. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "manfareed" wrote in message ... hi , I need to be able to set a print range according to the last column with data. The last used column varies with different entitities. The last row on the print range should always be 710. Thanks |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic print ranges ...
Ok Thanks
"Jon Peltier" wrote: My suggestion still works, or you could assign the print area to the used range of the worksheet: With ActiveSheet .UsedRange.Name = "'" & .Name & "'!Print_Area" End With then proceed to print. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "manfareed" wrote in message ... I think the question has been misinterpreted. This is to do with our financial reporting tool. A P&L is ran for each country but they all differ because they have different number of branches. For Example for France the last column would be at "AX" but if I ran the report for say the UK the last column would be "BM". I need a macro that sets the print range accordingly. Thanks "Jon Peltier" wrote: You can define a dynamic range for the print area. First define your area the usual way (File menu). Then go to Insert menu Names Define, and select Print_Area. Enter a formula like this in the Refers To box (replacing the hard-coded cell address): =OFFSET(Sheet1!$A$1,0,0,710,COUNT(Sheet1!$2:$2)) The print area will be as wide as you have elements in row 2, so if there are no blank cells in row 2, it will give the result you want, and ignore headers in row 1 that extend further than the data. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "manfareed" wrote in message ... hi , I need to be able to set a print range according to the last column with data. The last used column varies with different entitities. The last row on the print range should always be 710. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
setting print ranges to print based on option | Excel Programming | |||
Print Area ranges print on separate pages? | Excel Discussion (Misc queries) | |||
DYNAMIC RANGES | Excel Programming | |||
Dynamic Formulas with Dynamic Ranges | Excel Worksheet Functions | |||
Dynamic Print Ranges | Excel Programming |