Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Using ActiveSheet.UsedRange to define a print rang (Excel 97)

    I would like to incorporate ActiveSheet.UsedRange into a macro which sets certain print settings. Since I am dealing with over 100 spreadsheets (downloaded as *.prn files from an AS400) I would like to format them as identically as possible (number of rows varies from file to file). I've stepped through all the code I need for cell and print formatting, but I'm stumped when it comes to assigning a proper range. What can I do to automate/semi-automate this process?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using ActiveSheet.UsedRange to define a print rang (Excel 97)

    Is this something like what you want?

    <pre>Dim oSheet As Worksheet
    Application.DisplayAlerts = False
    For Each oSheet In Worksheets
    oSheet.PageSetup.PrintArea = oSheet.UsedRange.Address
    Next oSheet
    Application.DisplayAlerts = True
    </pre>

    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Using ActiveSheet.UsedRange to define a print rang (Excel 97)

    I just ran through my recorded code again and see that, even though I selected Relative Reference, the macro includes specific cell references. So I tried adding UsedRange to solve my problem
    With ActiveSheet.PageSetup
    .PrintTitleRows = "$1:$9"
    .PrintTitleColumns = ""
    ' ActiveSheet.PageSetup.PrintArea = "$A$10:$M$183"
    ActiveSheet.PageSetup.UsedRange.Address
    End With

    Produces the error message "Object doesn't support this property or method"
    Can you show me how to correct this error? THX

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using ActiveSheet.UsedRange to define a print rang (Excel 97)

    Try this:

    <pre> With ActiveSheet.PageSetup
    .PrintTitleRows = "$1:$9"
    .PrintTitleColumns = ""
    .PrintArea = ActiveSheet.UsedRange.Address
    End With
    </pre>

    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Using ActiveSheet.UsedRange to define a print rang (Excel 97)

    Looks like this will work, but I'll have to go through my code again to try to resolve the nearly absolute relative references that I created while formatting the cells in the sheet for printing.
    This must be the culprit now:
    ActiveCell.Offset(0, 1).Range("A1:L174").Select
    I've got one for each column I format.
    Is there something I can substitute for this statement that makes it less absolute-like? In Lotus, I recall anchoring the cursor, then using {End}{Down} several times then one {End}{Up} to highlight down to the last cell in a column. Any Excel equivalent? I appreciate your help.

  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Using ActiveSheet.UsedRange to define a print rang (Excel 97)

    Is it the last cell in the spreadsheet you want to go to?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using ActiveSheet.UsedRange to define a print rang (Excel 97)

    I'm not sure what you are asking. The code you gave selects an area of 12 columns and 174 rows that is offset one cell to the right of active cell. There is nothing absolute about that statement. However, your last couple of sentences talking about the Lotus code sounds like you are looking for code to select the cells from the active cell down through the last used cell in the column. If that is what you want, then this will do that:

    <pre> Range(ActiveCell, Cells(65536, ActiveCell.Column).End(xlUp)).Select
    </pre>


    If that is not what you want, could you try to reword your question.
    Legare Coleman

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Using ActiveSheet.UsedRange to define a print rang (Excel 97)

    Catherine, Yes this is the last cell of the workbook from which I recorded the macro. Each workbook has only one sheet. Since this is a P&L sheet for the various departments the columns are standardized but the rows will vary.
    Legare, I tried reworking your most recent suggestion into my existing code and it works just fine. Unfortunately for me, my recorded procedure is pretty ugly and tough to edit (at least for me it is). So I have restarted this macro by dividing the procedure into smaller chunks. Thank you for your (continued) help.

Posting Permissions

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