Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: vba print area
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2015
    Posts
    156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default vba print area

    hello all,
    i have a worksheet with column A-I, row 1-250.
    however i'm not necessary to use every row every time.

    the question is how can i simply set the print area automatically to last row thus i can use less paper?
    it seems my code below will goto row 250 every time and i will print "blank sheet" out

    Code:
    Dim LastRow As Long
        LastRow = Range("A:I").SpecialCells(xlCellTypeLastCell).Row
        ActiveSheet.PageSetup.PrintArea = "$A$1:$I$" & LastRow
    thanks for everyone.

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,720
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: vba print area

    Have you formatted down to row 250?
    If so, it thinks that is where the end is.

    Assuming that column A is always populated for any row that has data, maybe try calculating LastRow like this instead:
    Code:
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Mar 2015
    Posts
    156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba print area

    Quote Originally Posted by Joe4 View Post
    Have you formatted down to row 250?
    If so, it thinks that is where the end is.

    Assuming that column A is always populated for any row that has data, maybe try calculating LastRow like this instead:
    Code:
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    thanks Joe
    seems not working what i want.
    i posted a picture here and my worksheet like this, what if i want to print A9:I14 in 1sheet, is it possible or any idea that make my request success

    thanks,

    Code:
    Sub macro1()
    
    Dim LastRow As Long
        'LastRow = Range("A:I").SpecialCells(xlCellTypeLastCell).Row
        LastRow = Cells(Rows.Count, "A").End(xlUp).Row
        ActiveSheet.PageSetup.PrintArea = "$A$1:$I$" & LastRow
    End Sub


  4. #4
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,065
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: vba print area

    Maybe...

    Code:
    Sub macro1B()
        ActiveSheet.PageSetup.PrintArea = Range("A9:I" & Range("B" & Rows.Count).End(xlUp).Row).Address
    End Sub
    Last edited by MARK858; Sep 6th, 2019 at 01:30 AM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  5. #5
    Board Regular
    Join Date
    Mar 2015
    Posts
    156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba print area

    Quote Originally Posted by MARK858 View Post
    Maybe...

    Code:
    Sub macro1B()
        ActiveSheet.PageSetup.PrintArea = Range("A9:I" & Range("B" & Rows.Count).End(xlUp).Row).Address
    End Sub
    it's still hit to row 250

  6. #6
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,065
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: vba print area

    Then what have you got in row 250, in particular column B? Do you have formulas returning ""?
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  7. #7
    Board Regular
    Join Date
    Mar 2015
    Posts
    156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba print area

    Quote Originally Posted by MARK858 View Post
    Then what have you got in row 250, in particular column B? Do you have formulas returning ""?
    i have a formula in column B, D, E, F, G & G to return what i need in database sheet.
    before i try your code, i have this to turn all formulas no matter on blank/non-blank cell to value.

    Code:
    Sub Macro18()    Cells.Select
        Range("A9").Activate
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("A9").Select
    End Sub
    thanks Mark

  8. #8
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,065
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: vba print area

    No you don't need to change anything at this stage, just answer the question as to what is in B250? is it a formula returning "" once you have answered that then we can (depending on your answer) amend the code.
    Last edited by MARK858; Sep 7th, 2019 at 08:07 AM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  9. #9
    Board Regular
    Join Date
    Mar 2015
    Posts
    156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba print area

    Quote Originally Posted by MARK858 View Post
    No you don't need to change anything at this stage, just answer the question as to what is in B250? is it a formula returning "" once you have answered that then we can (depending on your answer) amend the code.
    yes, a formula return "" iferror in the original

    Last edited by kelvin_9; Sep 7th, 2019 at 09:01 PM.

  10. #10
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,065
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: vba print area

    Try...

    Code:
    Sub macro1B()
        ActiveSheet.PageSetup.PrintArea = Range("A9:I" & Columns(2).Find("*", , xlValues, , xlByRows, xlPrevious).Row).Address
    End Sub
    Untested and expect typos as I am on my phone.
    Last edited by MARK858; Sep 8th, 2019 at 01:40 AM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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