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

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

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

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

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

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

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

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