Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54

    Question Unanswered: dynamically setting the print area using vba

    I've got some code that prepares an excel spreadsheet for its distribution by email. If I prepare the file manually I lock the worksheets leaving only certain cells unlocked, I set the color of the columns around the area I'd like the user to edit to some other color (like dark gray), and I set the print area to the list in the first five columns. I've been able to figure out how to do all of that except the last step using VBA. the trouble is the range changes for each data set (though it is always those five columns). I can use the following code to select the range dynamicall:

    Code:
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    but I'd like to be able to set the print area to that selection and I can't figure out how to do that. I was hoping to be able to do something like:

    Code:
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    
    ActiveSheet.PageSetup.PrintArea = Selection
    But that doesn't work. Your help is greatly appreciated! Thanks!

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Try this

    ActiveSheet.PageSetup.PrintArea = Selection.Address

    ??

    MTB

  3. #3
    Join Date
    Jan 2008
    Location
    Denver, CO
    Posts
    54
    Nice... didn't realize just how close I was... that did it! Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •