Set Print Area Macro

G

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
 
F

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
 
D

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

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.
Click to expand...

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

Grace

Works nicely. Thanks Don, and Thomas.

Grace

Don Guillett said:
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)
Click to expand...
 
G

Grace

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

Thanks,

Grace
 
D

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

Grace

Thanks Dave and Frank

Dave Peterson said:
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.
Click to expand...
 
Top