Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Feb 2003
    Location
    Bournemouth, Dorset, England
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Setting Print Area (Xl 97)

    Hi all,

    I need help with a macro that can set the print area of a sheet, where the number of columns is constant, but the number of rows can change from day-to-day or month-to-month.
    Does anyone know of a way I can set up a macro to read the number of rows of data, and set the print area based on this reading? This is easy to do manually by just moving the blue print region lines when in Page Break Preview mode, but for users with very limited knowledge, I'd prefer a button they can press that will automate the process for them.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting Print Area (Xl 97)

    Somethingl ike this:

    ActiveSheet.PageSetup.PrintArea = ActiveSheet.UsedRange.Address
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Setting Print Area (Xl 97)

    This code will set the print area of the current sheet from C1-the last occupied row in col C (c = 3) and then expand it to 8 cols (to col H).

    <pre>Option Explicit
    Sub SetPrintArea()
    Dim sRange As String
    Dim iStartCol As Integer
    Dim iNumCols As Integer

    iStartCol = 3
    iNumCols = 8

    sRange = Range(Cells(1, iStartCol), _
    Cells(65536, iStartCol).End(xlUp)). _
    Resize(, iNumCols).Address

    ActiveSheet.PageSetup.PrintArea = sRange
    End Sub</pre>


    Change the Start col (3 in example) and number of columns (8 in example) as desired
    Steve

Posting Permissions

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