dynamic print ranges ...

[manfareed]

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

 

[Bob Phillips]

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)

 

[Jon Peltier]

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]

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

 

[Bob Phillips]

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)

 

[manfareed]

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]

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

 

[manfareed]

Ok Thanks

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
_______