I have a routine that ultimately selects a range of cells that I want to use
for printing. After highlighting that block, how do I define that range so
that it is set as a print area. When I use record macro, even with relative
references, it seems to only record the, say, six column range that was
selected THIS time, such as F5:g10. Next time, with different data, the
macro will find a longer or shorter same six-column range and I need code
that knows that.

Thanks,
Grace

Hi
try something like the following:
sub foo()
dim rng as range
with activesheet
set rng=selection
.PageSetup.PrintArea = rng.address
.printout
end with
end sub

--
Regards
Frank Kabel
Frankfurt, Germany


Grace wrote:
I have a routine that ultimately selects a range of cells that I want
to use for printing. After highlighting that block, how do I define
that range so that it is set as a print area. When I use record
macro, even with relative references, it seems to only record the,
say, six column range that was selected THIS time, such as F5:g10.
Next time, with different data, the macro will find a longer or
shorter same six-column range and I need code that knows that.

Thanks,
Grace

On my last post, I meant to use

F5:K10, not F5:g10. Sorry

Grace

"Grace" wrote in message
...
I have a routine that ultimately selects a range of cells that I want to
use
for printing. After highlighting that block, how do I define that range
so
that it is set as a print area. When I use record macro, even with
relative
references, it seems to only record the, say, six column range that was
selected THIS time, such as F5:g10. Next time, with different data, the
macro will find a longer or shorter same six-column range and I need code
that knows that.

Thanks,
Grace

example of how to find the last row in a column

x=cells(rows.count,"a").end(xlup).row
'to use in range
range("a1:g" & x)

--
Don Guillett
SalesAid Software

"Grace" wrote in message
...
I have a routine that ultimately selects a range of cells that I want to
use
for printing. After highlighting that block, how do I define that range
so
that it is set as a print area. When I use record macro, even with
relative
references, it seems to only record the, say, six column range that was
selected THIS time, such as F5:g10. Next time, with different data, the
macro will find a longer or shorter same six-column range and I need code
that knows that.

Thanks,
Grace

Works nicely. Thanks Don, and Thomas.

Grace

"Don Guillett" wrote in message
...
example of how to find the last row in a column

x=cells(rows.count,"a").end(xlup).row
'to use in range
range("a1:g" & x)

--
Don Guillett
SalesAid Software

"Grace" wrote in message
...
I have a routine that ultimately selects a range of cells that I want to
use
for printing. After highlighting that block, how do I define that range
so
that it is set as a print area. When I use record macro, even with
relative
references, it seems to only record the, say, six column range that was
selected THIS time, such as F5:g10. Next time, with different data, the
macro will find a longer or shorter same six-column range and I need
code
that knows that.

Thanks,
Grace

When I added option explicit, as many suggest I do, it says x is undefined.
How should it be defined?

Thanks,

Grace

"Don Guillett" wrote in message
...
example of how to find the last row in a column

x=cells(rows.count,"a").end(xlup).row
'to use in range
range("a1:g" & x)

--
Don Guillett
SalesAid Software

"Grace" wrote in message
...
I have a routine that ultimately selects a range of cells that I want to
use
for printing. After highlighting that block, how do I define that range
so
that it is set as a print area. When I use record macro, even with
relative
references, it seems to only record the, say, six column range that was
selected THIS time, such as F5:g10. Next time, with different data, the
macro will find a longer or shorter same six-column range and I need
code
that knows that.

Thanks,
Grace

Hi
you have to use
Dim x
for this. See the VBA help for 'Dim'

--
Regards
Frank Kabel
Frankfurt, Germany


Grace wrote:
When I added option explicit, as many suggest I do, it says x is
undefined. How should it be defined?

Thanks,

Grace

"Don Guillett" wrote in message
...
example of how to find the last row in a column

x=cells(rows.count,"a").end(xlup).row
'to use in range
range("a1:g" & x)

--
Don Guillett
SalesAid Software

"Grace" wrote in message
...
I have a routine that ultimately selects a range of cells that I
want to use for printing. After highlighting that block, how do I
define that range so that it is set as a print area. When I use
record macro, even with relative references, it seems to only
record the, say, six column range that was selected THIS time, such
as F5:g10. Next time, with different data, the macro will find a
longer or shorter same six-column range and I need code that knows
that.

Thanks,
Grace

I'd use:

Dim x As Long

Long represents a whole number between -2,147,483,648 and 2,147,483,647.

And that's enough for any row number (1 to 65536).

You can look for "Data Type Summary" in VBA's help to see lots of different
types.

Grace wrote:

When I added option explicit, as many suggest I do, it says x is undefined.
How should it be defined?

Thanks,

Grace

"Don Guillett" wrote in message
...
example of how to find the last row in a column

x=cells(rows.count,"a").end(xlup).row
'to use in range
range("a1:g" & x)

--
Don Guillett
SalesAid Software

"Grace" wrote in message
...
I have a routine that ultimately selects a range of cells that I want to
use
for printing. After highlighting that block, how do I define that range
so
that it is set as a print area. When I use record macro, even with
relative
references, it seems to only record the, say, six column range that was
selected THIS time, such as F5:g10. Next time, with different data, the
macro will find a longer or shorter same six-column range and I need
code
that knows that.

Thanks,
Grace





--

Dave Peterson

Thanks Dave and Frank

"Dave Peterson" wrote in message
...
I'd use:

Dim x As Long

Long represents a whole number between -2,147,483,648 and 2,147,483,647.

And that's enough for any row number (1 to 65536).

You can look for "Data Type Summary" in VBA's help to see lots of
different
types.

Grace wrote:

When I added option explicit, as many suggest I do, it says x is
undefined.
How should it be defined?

Thanks,

Grace

"Don Guillett" wrote in message
...
example of how to find the last row in a column

x=cells(rows.count,"a").end(xlup).row
'to use in range
range("a1:g" & x)

--
Don Guillett
SalesAid Software

"Grace" wrote in message
...
I have a routine that ultimately selects a range of cells that I
want to
use
for printing. After highlighting that block, how do I define that
range
so
that it is set as a print area. When I use record macro, even with
relative
references, it seems to only record the, say, six column range that
was
selected THIS time, such as F5:g10. Next time, with different data,
the
macro will find a longer or shorter same six-column range and I need
code
that knows that.

Thanks,
Grace





--

Dave Peterson

Grüezi Grace

Grace schrieb am 11.06.2004

I have a routine that ultimately selects a range of cells that I want to use
for printing. After highlighting that block, how do I define that range so
that it is set as a print area. When I use record macro, even with relative
references, it seems to only record the, say, six column range that was
selected THIS time, such as F5:g10. Next time, with different data, the
macro will find a longer or shorter same six-column range and I need code
that knows that.

Do you print in VBA?

Then you could use the following:

Selection.PrintOut

--
Regards

Thomas Ramel
- MVP for Microsoft-Excel -

[Win XP Pro SP-1 / xl2000 SP-3]