Set Print Area Macro

[Grace]

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

 

[Frank Kabel]

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

 

[Grace]

On my last post, I meant to use

F5:K10, not F5:g10. Sorry

Grace

 

[Don Guillett]

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)

 

[Thomas Ramel]

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]

 

[Grace]

Works nicely. Thanks Don, and Thomas.

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)

 

[Grace]

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

Thanks,

Grace

 

[Frank Kabel]

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

 

[Dave Peterson]

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]

Thanks Dave and Frank

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.