Dynamically setting Print Area
 

I have a spreadsheet that has a fixed number of rows, but
a variable number of columns that may contain data. I am
trying to set the print area depending on how many columns
have data. I have a looping structure in my code which
moves through the columns and locates the last column
containing data. This loop works correctly, but it returns
a number.

The printarea wants the R1C1 cell notation.

I could preset my print area but more than likely in 90%
of the cases, only half of the available columns or less
will be used. I don't want to print the whole spreadsheet
unless I need to so the printouts will be easy to read.

I have tried using the Activecell.Address property and
other methods to no avail. I am sure it is just a syntax
problem, but I am not finding any good examples in the
Helpless Help!

Help!

Thanks in advance.

Kevin

Dynamically setting Print Area
 

assume the lastcolumn is contained in the Variable
vCol
change 25 to the number of rows.

activesheet.PageSetup.printArea =
Range("A1",Cells(25,vCol)).Address(External:=True)

--
Regards,
Tom Ogilvy


"Kevin" wrote in message
...
I have a spreadsheet that has a fixed number of rows, but
a variable number of columns that may contain data. I am
trying to set the print area depending on how many columns
have data. I have a looping structure in my code which
moves through the columns and locates the last column
containing data. This loop works correctly, but it returns
a number.

The printarea wants the R1C1 cell notation.

I could preset my print area but more than likely in 90%
of the cases, only half of the available columns or less
will be used. I don't want to print the whole spreadsheet
unless I need to so the printouts will be easy to read.

I have tried using the Activecell.Address property and
other methods to no avail. I am sure it is just a syntax
problem, but I am not finding any good examples in the
Helpless Help!

Help!

Thanks in advance.

Kevin

Dynamically setting Print Area
 

Tom,

Thanks! I figured it out from an answer Ron de Bruin gave
Pam eralier today. I used:
Worksheets(1).Range(Cells(1, 1), Cells(52,
curCol)).PrintOut
(Excuse wordwrapping)

I want to thank everyone who helps answer questions on
this forum! I learn a lot from reading answers you all
give to other people as well as from my own posted
questions!

Thanks Again!

Kevin
-----Original Message-----
I have a spreadsheet that has a fixed number of rows, but
a variable number of columns that may contain data. I am
trying to set the print area depending on how many
columns
have data. I have a looping structure in my code which
moves through the columns and locates the last column
containing data. This loop works correctly, but it
returns
a number.

The printarea wants the R1C1 cell notation.

I could preset my print area but more than likely in 90%
of the cases, only half of the available columns or less
will be used. I don't want to print the whole spreadsheet
unless I need to so the printouts will be easy to read.

I have tried using the Activecell.Address property and
other methods to no avail. I am sure it is just a syntax
problem, but I am not finding any good examples in the
Helpless Help!

Help!

Thanks in advance.

Kevin
.

Dynamically setting Print Area
 

That will print your range, but it won't set the printarea (what you asked).
However, it you are happy, I'm ecstatic.

If you are going to qualify Range, then you should qualify all references

With Worksheets(1)
.Range(.Cells(1, 1), .Cells(52, curCol)).PrintOut
End With

otherwise don't qualify anything (and thus all references refer to the sheet
containing the code (if in the sheet module) or to the activesheet (if in a
general module).

Range(Cells(1, 1), Cells(52, curCol)).PrintOut


--
Regards,
Tom Ogilvy


"Kevin" wrote in message
...
Tom,

Thanks! I figured it out from an answer Ron de Bruin gave
Pam eralier today. I used:
Worksheets(1).Range(Cells(1, 1), Cells(52,
curCol)).PrintOut
(Excuse wordwrapping)

I want to thank everyone who helps answer questions on
this forum! I learn a lot from reading answers you all
give to other people as well as from my own posted
questions!

Thanks Again!

Kevin
-----Original Message-----
I have a spreadsheet that has a fixed number of rows, but
a variable number of columns that may contain data. I am
trying to set the print area depending on how many
columns
have data. I have a looping structure in my code which
moves through the columns and locates the last column
containing data. This loop works correctly, but it
returns
a number.

The printarea wants the R1C1 cell notation.

I could preset my print area but more than likely in 90%
of the cases, only half of the available columns or less
will be used. I don't want to print the whole spreadsheet
unless I need to so the printouts will be easy to read.

I have tried using the Activecell.Address property and
other methods to no avail. I am sure it is just a syntax
problem, but I am not finding any good examples in the
Helpless Help!

Help!

Thanks in advance.

Kevin
.