Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Mar 2003
    Location
    Wilmington, North Carolina, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Not Printing Cells

    I've created a spreadsheet in Excel 97 that contains formulas in column A. These formulas will insert a unique identifying number when people input data into other cells on the row. The formulas go from A1 to A500. Unfortunately as data is inputted into the rows and the report is printed, lots of blank pages come out after the last row of data. (Excel is printing up to the last active cell). Is there a way to print only the rows that have data and not those rows that have formulas? Hopefully someone can help me or tell me not to even continue thinking about this.

  2. #2
    Star Lounger
    Join Date
    Apr 2001
    Location
    Winchester, Hampshire, United Kingdom
    Posts
    64
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Not Printing Cells

    If I understand your problem correctly, setting print area should solve this. I use a number of worksheets that use a similar procedure, and they all print fine because the print area is set to only the cells I want printed. You may need to rejig the worksheet so the print area is contiguous, but that should solve it. If that doesn't work for some reason, try putting the formulae on another sheet and linking to the relevant cells you want printed.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not Printing Cells

    The Offset/CountA method, useful for Pivot Tables and Graphs, should work here. If you set the print area to:

    =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$B:$[img]/forums/images/smilies/cool.gif[/img],8)

    XL should print 8 columns wide and as many rows as there are entries in Column B. May need modifying if the last row with an entry is in a column other than Column B.

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

    Re: Not Printing Cells

    When I print a sheet with hidden columns, the hidden columns do not print. What are you doing to gete them to print?
    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not Printing Cells

    Hi VBA Group:
    I have a few questions re printing only visible cells in a named range:

    1) FIRST, how do you set the print range? I tried the following code and got the error message "expected end of statement" before I put in the "Activesheet.Printout" statement:

    Private Sub CommandButton3_Click()
    Worksheets("ALL").Activate
    Worksheets("ALL").PageSetup.PrintArea = _
    "A_Print_AN".Address

    Activesheet.Printout
    End Sub

    Would Activesheet.Printout do the job or is "Active.Address.PrintOut" a legitimate statement?

    2) How would you select a named range that is defined by Offset and counta, as in the above post? I have found that I can not even use Print_Area to select such defined ranges (you can't even use the Goto function).

    3) My client does not want to see all the columns in the tables I have created. It is easy to Hide the extra ones but what VBA code should I use to cause only the visible columns to print?

    Thanking you in advance,
    Stephen
    Stephen

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not Printing Cells

    I am not doing. I am getting ready and I don't know how to do. I have never written any code to print anything and the books I have do not even address the subject and the Help files are very brief.
    In short, I don't know what I am doing.
    Thanks

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

    Re: Not Printing Cells

    1- Your code was close:

    <pre>Private Sub CommandButton3_Click()
    Worksheets("ALL").Activate
    Worksheets("ALL").PageSetup.PrintArea = Range("A_Print_AN").Address

    ActiveSheet.PrintOut
    End Sub
    </pre>


    2- I don't know about number 2.


    3- Hidden columns should not print.
    Legare Coleman

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not Printing Cells

    Legare:
    I tried the following (adapted from your code):
    Private Sub CommandButton3_Click()
    'button is in WS "ALL" but range is in the WS "ANUsers"
    Worksheets("ANUsers").Activate
    Worksheets("ANUsers").PageSetup.PrintArea = Range("Static_An_Range").Address
    'Selection.PrintOut Copies:=1, Collate:=True
    ActiveSheet.PrintOut
    End Sub

    "Static_An_Range" is a non-dynamically defined Range. This did not work so I tried sticking the code in a module and calling it from the main program but it still did not work.
    Any suggestions?
    Stepehen

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Not Printing Cells

    Stephen, hope you don't mind if i but in. I tried your code and it printed as I expected. What way is it failing you ?

    Andrew

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Not Printing Cells

    Stephen, Sorry but I just ran it from a general module, with the sheet the slected sheet. Try the following in your Button event :-

    Worksheets("ANUsers").PageSetup.PrintArea = Worksheets("ANUsers").Range("Static_An_Range").Address

    Andrew

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not Printing Cells

    Legare:
    Hey! I'm OK now. The following worked even with the dynamically defined areas:

    This worked even with the Dynamically defined Range:

    Sub Print_AN()
    Worksheets("ANUsers").Activate
    Worksheets("ANUsers").Range("A_Print_AN").Select
    Worksheets("ANUsers").PageSetup.PrintArea = Range("A_Print_AN").Address
    Selection.PrintOut Copies:=1, Collate:=True
    End Sub

    I put the cmdClick button in the "All" WS and had it call each of the tables I wanted to print (one in each WS "ANUsers", "PNUsers", Etc.).
    Thanks to you and Andrew, I am back on track.
    Cheers, till we meet again.
    Stehen

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not Printing Cells

    Andrew:
    Problem = Lack of confidence: I had never written VBA code to print & I was so sure that I would not be able to print dynamic ranges, that I missed the fact that I had not activated the WS where the table was (I was sure it was a more profound bit of knowledge that I lacked.
    I hope that, recognition of this type of response, will stop me from sending you questions that I can answer myself in the future.
    Sorry <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

Posting Permissions

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