Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Set Print Area Code (Excel 2002)

    I have written the following code that works well when I want to have the print area reset every time the size of my data changes. This of course works well when it's only the rows that change, however now my column is variable as well. For example, in this code example it has column H hard coded, and I need to make the H go away and make it variable as I did with the rows. The column may be K next time, or C... one never knows. I'm really just trying to set a new print area every time the data changes, and BOTH the rows AND the columns are variable this time. Please note that A1 will always be where the range will start, as this is where report titles are located. Any help is appreciated as always!!
    Thanks!
    Lana

    Sub SetPrintArea()
    Dim x As Long
    Range("A65536").Select
    x = -Range(Selection, Selection.End(xlUp)).Rows.Count + 65537
    Range("A1:H" & x).Select
    ActiveSheet.PageSetup.PrintArea = "$A$2:$H$" & x
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set Print Area Code (Excel 2002)

    Assuming that row 1 contains column headers for the columns you want to include, try

    Dim r As Long
    Dim c As Long
    r = Range("A65536").End(xlUp).Row
    c = Range("IV1").End(xlToLeft).Column
    ActiveSheet.PageSetup.PrintArea = Range(Cells(2, 1), Cells(r, c)).Address

    But perhaps this much simpler version will work:

    ActiveSheet.PageSetup.PrintArea = Range("A1").CurrentRegion.Address

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Set Print Area Code (Excel 2002)

    Hi,
    I can't seem to get either one to work. It may be because the set print area I'm trying to acheive is on a title and a pivot table. I have report titles always located in cell A1, however the pivot table data can vary as to it's length and width. I've also discovered (with my other method of set print area) that with a pivot table, my code doesn't recognized the bottom of the pivot table if that part of the table is empty (no data in it, but it is technically still part of the pivot table with the formats, etc). Anyway, it does appear that most of the pivot tables have the field names in row 7, so I tried to change your code below to "IV7" as opposed to "IV1" thinking that would work and it didn't. I don't get any error messages... it actually does something, but when I look at the worksheet, nothing is highlighted. I'm thinking I need to change the 2,1 in the last line... I tried this out but my efforts failed. Any ideas on what to try next??
    Thanks so much!
    Lana

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set Print Area Code (Excel 2002)

    We'd need to see a sample workbook.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Set Print Area Code (Excel 2002)

    Attached are three pivot table examples.
    Thanks!
    Lana
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set Print Area Code (Excel 2002)

    I don't understand why you need to set the print area, for in each of the examples, it is the entire used range of the sheet, and Excel will use this automatically.

    But you could use

    ActiveSheet.PageSetup.PrintArea = ActiveSheet.UsedRange.Address

    If you'd rather set the area explicitly, you could use

    Dim r As Long
    Dim c As Long
    r = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    c = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
    ActiveSheet.PageSetup.PrintArea = ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(r, c)).Address

    You can, of course, replace ActiveSheet with another sheet or with a variable that refers to a sheet.

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Set Print Area Code (Excel 2002)

    Okay... you could have blown me over with a feather with this one, as I did NOT know that Excel does it automatically. I've been setting print areas for so many years, I think I grew to think I had to set them!!! Good news is that I was smart enough to figure out how to clear the old Set Print Areas (ActiveSheet.PageSetup.PrintArea = Clear), so Excel could do it automatically from now on, and I don't need to add it to any of my macros... Wow... learn something simple every day!!
    Thanks so much Hans!!
    Lana

Posting Permissions

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