ExcelBanter - View Single Post

manfareed

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