+ Reply to Thread
Results 1 to 7 of 7

How can I auto adjust the printed page size with VBA?

  1. #1
    Registered User
    Join Date
    01-21-2021
    Location
    Columbia, MO
    MS-Off Ver
    365
    Posts
    4

    How can I auto adjust the printed page size with VBA?

    I have a tool that has a couple of macro buttons. One of these buttons adds a group of columns to the sheet. Another button then exports the excel sheet to a .pdf file. When exporting, the code only prints the original number of columns. How can I adjust the VBA code to capture all of the applicable columns?
    Attached Files Attached Files
    Last edited by Finnell; 04-16-2021 at 11:47 AM.

  2. #2
    Valued Forum Contributor rollis13's Avatar
    Join Date
    01-26-2012
    Location
    Cordenons
    MS-Off Ver
    Excel 2016 32bit - Win 11
    Posts
    907

    Re: How can I auto adjust the printed page size with VBA?

    This is where your macro sets up your column static print area:
    Please Login or Register  to view this content.
    Last edited by rollis13; 04-15-2021 at 06:11 PM.

  3. #3
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,496

    Re: How can I auto adjust the printed page size with VBA?

    Your sheet is loaded with "Merge & Center" cells. Accidents waiting to happen. Get rid of them and use "Center across Selection" instead. (Google it and you'll see what the experts think of it.)
    There are different ways to calculate your last used Column and Row.
    In your attachment you dont want to print up to the laste used Column because that is Column R which is used for the conversions.
    It looks like the best way would be to find the Column in row 24.
    If so, the last column could be calculated with
    Please Login or Register  to view this content.
    If it needs to be another row, change the 24 to the row number that suit your needs the best. (NOT merged cells)
    If you have the conversion table in another sheet or all to the left of your print area, following would be the best way.
    Please Login or Register  to view this content.
    For your last used row, it looks like there will always be data in Column A so the last used row is calculates as
    Please Login or Register  to view this content.
    If there is never going to be anything below the area to be printed, you can use this also
    Please Login or Register  to view this content.
    So your PrintArea would be like
    Please Login or Register  to view this content.
    or equivalent.

    BTW. 20 meters is not 6.1 ft. The other way around is closer to the truth.
    Last edited by jolivanes; 04-15-2021 at 10:30 PM. Reason: Added info

  4. #4
    Registered User
    Join Date
    01-21-2021
    Location
    Columbia, MO
    MS-Off Ver
    365
    Posts
    4

    Re: How can I auto adjust the printed page size with VBA?

    Thanks for the help. This works beautifully.

  5. #5
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,496

    Re: How can I auto adjust the printed page size with VBA?

    Thanks for letting us know that it worked.

    These sites are to help people, just like you were helped.
    If you would look for help again, found a thread that had the same problem solved that you are looking to solve but they had deleted part of the explanation, in your case the attachment, would you be happy?
    I don't know why you decided to delete the attachment, I did not find anything personal in it, but you deprive other people from possibly being helped.

  6. #6
    Registered User
    Join Date
    01-21-2021
    Location
    Columbia, MO
    MS-Off Ver
    365
    Posts
    4

    Re: How can I auto adjust the printed page size with VBA?

    Hey jolivanes, I had realized that I left company contact/address information in the sheet and didn't want that displayed. I will reupload a version without it. Sorry for the confusion.

  7. #7
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,496

    Re: How can I auto adjust the printed page size with VBA?

    Very much appreciated. Thank you.
    Yes, never have any personal information in a post and/or attachment.
    Skammers love it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Auto adjust font size in a text box.
    By sungen99 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-06-2019, 03:44 PM
  2. Auto page setup/row height adjust
    By alfgrey in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-17-2014, 06:55 AM
  3. Auto Adjust Excel Page area
    By Drayde in forum Excel General
    Replies: 3
    Last Post: 03-17-2014, 09:23 AM
  4. auto-adjust cell size
    By StrawberryFields in forum Excel General
    Replies: 1
    Last Post: 05-31-2011, 01:01 PM
  5. font size auto adjust
    By loner2003 in forum Excel General
    Replies: 0
    Last Post: 09-21-2010, 02:17 PM
  6. Auto size a pivot table to a page size
    By *.* in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-23-2006, 12:09 AM
  7. [SOLVED] Auto adjust rows to size of contents
    By Howjos in forum Excel General
    Replies: 6
    Last Post: 07-04-2006, 12:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1