Excel VBA - Setting Print Area in VB

[spikel]

I have a problem with being able to set the print area using VB.

The Project I am working on has a sheet (Sheet 1) where details are
entered, and a second sheet (Sheet 2) picks out certain details using
VLOOKUP from Sheet 1. Sheet 1 (and therefore Sheet 2), can have from a
minimum of 3 rows anywhere up to 400 rows. I need to be able to print
an amount of rows inbetween 3 and 400 from Sheet 2.

On Sheet 2 , there are columns which have details in them, but also
have VLOOKUP formlas in them, so using VB to find the bottom of the
column doesn't help here, as it always finds the 400th row, unless
there is a way to search for a blank value!?!?!? I do however have a
column which is copyed from Sheet 1 and pasted in column P on Sheet 2,
these are already values (text).

Using Offsets from column P, I can select the area for which I want to
be printed (this area goes from the bottom of the table to Cell B13),
but I cannot set this as the print area! I have tried coding like...
ActiveSheet.PageSetup.PrintArea = CurrentRegion
and
ActiveSheet.PageSetup.PrintArea = CurrentSelection
either followed by
ActiveSheet.PrintOut Copies:=1
but this still prints all 400 rows!?

Also the page setup is already set to landscape, it is on A4, and the
width of the columns fits on one page.

Any help would be very helpful, i'm doing my nut in and I can't figure
it!!!!

 

[George Nicholson]

Per Help, PrintArea is looking for the ADDRESS of a range (a string), not
the range (an object). Try

ActiveSheet.PageSetup.PrintArea = CurrentRegion.Address
OR
ActiveSheet.PageSetup.PrintArea = CurrentSelection.Address

and see if those work.

FYI, the example in Help provides this as an example:
ActiveSheet.PageSetup.PrintArea = ActiveCell.CurrentRegion.Address
I don't know if using ActiveCell is 100% required, but in case you have
further problems....

HTH,

 

[spikel]

Thanks for the help George! will try this out!