Results 1 to 6 of 6
  1. #1
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Range to Print (Excel 2000)

    As a tyro with VBA (thanks to a recent posting I am taking the tutorial!) I need help as follows. In one workbook I have 5 "Order" sheets, where data is pasted. 5 Print sheets take certain data from the order sheets so it is laid out in the correct sequence for computer input. This is done by using If formulas in the print sheets, e.g. IF(O1!$a2= "","",O1$A2) etc. This is filled down for 200 lines.
    How can I define the print range, which will vary each week, so I can have a macro to set the correct print range? I cannot use end down, as it will go down to the last line with formulas
    Many thanks for your help.
    Paul Coyle
    Approach love and cooking with reckless abandon

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

    Re: Range to Print (Excel 2000)

    You could use code like below to find the last row that is not empty:

    <pre>Dim I As Long, lIMax As Long
    lIMax = Worksheets("P1").Range("A65536").End(xlUp).Row
    For I = lIMax - 1 To 0 Step -1
    If Worksheets("P1").Range("A1").Offset(I, 0).Value <> "" Then
    Exit For
    End If
    Next I
    </pre>


    This code assums that the sheet containing the formulas is named P1.
    Legare Coleman

  3. #3
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Range to Print (Excel 2000)

    Thanks Legare for you prompt response, I think I understand what you are doing.

    <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18> <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Paul Coyle
    Approach love and cooking with reckless abandon

  4. #4
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Range to Print (Excel 2000)

    Hi Legare,
    I have been testing the code you were so kind to send and have come up with a problem, as follows.
    The code appears to be sensitive to where the active cell is. If the active cell is A1, the the range starts in A1. If the active cell is d4, then the selection is d1.d4 etc.
    I suspect my tyro VBA attempt is awry, so I have attached the file, the macro is called PRange
    <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18> <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15>
    Attached Files Attached Files
    Paul Coyle
    Approach love and cooking with reckless abandon

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

    Re: Range to Print (Excel 2000)

    My code just found the offset of the last row that the formula does not display empty. The code you added works with the selection and is definitely sensitive to what is the active cell, and never used I which is the offset of the last row to print. If your print always starts in A1 and goes to column J and the last row with something displayed in column A, then you can use the following code to select the area to print:

    <pre>Sub PRange()
    Dim I As Long, lIMax As Long
    lIMax = Worksheets("P1").RANGE("A65536").End(xlUp).Row
    For I = lIMax - 1 To 0 Step -1
    If Worksheets("P1").RANGE("A1").Offset(I, 0).Value <> "" Then
    Exit For
    End If
    Next I
    Worksheets("P1").RANGE("A1:J" & I + 1).Select
    End Sub
    </pre>


    If you actually want to set the print area for the sheet, you could use something like this:

    <pre>Sub PRange()
    Dim I As Long, lIMax As Long
    lIMax = Worksheets("P1").RANGE("A65536").End(xlUp).Row
    For I = lIMax - 1 To 0 Step -1
    If Worksheets("P1").RANGE("A1").Offset(I, 0).Value <> "" Then
    Exit For
    End If
    Next I
    Worksheets("P1").PageSetup.PrintArea = "A1:J" & I + 1
    End Sub
    </pre>

    Legare Coleman

  6. #6
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Range to Print (Excel 2000)

    Thank you Legare, it works fine. I have edited your code to work with my numerous order sheets and all was AOK.
    I can now pass over this job to the department involved, knowing that all the orders will always print, and that superfluous pages will not be printed. <img src=/S/joy.gif border=0 alt=joy width=23 height=23>
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> & Kind regards for your <img src=/S/clever.gif border=0 alt=clever width=15 height=15> assistance
    <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

Posting Permissions

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