Need help creating a variable size printarea marco.

[E.Sortland]

Hi, hopefully someone can point me in the right direction.

I'm having trouble making a macro for a variable size print area.

My code so far:
Range("B8").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.PageSetup.PrintArea = "$B$8:$H$273"
Range("G5").Select

In the example above when i create the macro i have text in the area from B8
to H273, but if my text changes, the macro still selects this range to
printarea, making the area either to big or to small.

I have tried using this command from another workbook, but the results are
not what I'm looking for:
ActiveSheet.PageSetup.PrintArea = ActiveCell.CurrentRegion.Address

 

[Vergel Adriano]

You don't need the lines that select ranges.. Try it this way

ActiveSheet.PageSetup.PrintArea = ActiveSheet.UsedRange.Address

 

[Tom Ogilvy]

Assuming the code you posted selects the proper printarea, then

Range("B8").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.PageSetup.PrintArea = Selection.Address
Range("G5").Select

 

[E.Sortland]

This selects all everything in the workbook. I have some text boxes outside
the selected range that gets included, even though they have been set as do
not print in their properties using your suggestion... ?

Some more background. I have "static" information in A1-H7 and A8-A315

This has been entered into print setup "titles" (norwegian excel so I do not
know the exact wording in english)

So i want to select printarea from B8 - Bx to H8 - Hx. x can be anything
from 8 to 322. Setting this manually works without problems.

Also: the area D8-H322 contains forumlas used to look up SKU numbers in
B8-322, so they contain "information" even when displaying "".

Just need to know the "code" for a the current "selection-range".

Best regards,
E.Sortland

 

[E.Sortland]

Exactly what I was looking for! Thank you very much.

Best regards,
Espen

 

[Vergel Adriano]

Sorry, I missed that in your question. You do need the lines that select
ranges after all. Anyway, see Tom Ogilvy's response to your question. That
should work. Post back if it still doesn't get you what you're looking for..