Avatar of Rayne
Rayne
Flag for United States of America asked on

Print Area Reset

Hello All,

I am trying to set the print area dynamically to the worksheet. I have got the code so far in the attached file. I think I am not doing something right. Please be aware that the numbers of rows will change and so will the number of columns to the right. Sometimes the last column will be P, sometimes R or Q. The print area is not expanding to the right and to the down.
Thanks
PrintArea.xlsm
Microsoft OfficeMicrosoft Excel

Avatar of undefined
Last Comment
Rayne

8/22/2022 - Mon
Rayne

ASKER
please note the last column is AA but it can become Z or Y depending
dlmille

Ok - so what is the right answer?  For the CURRENT EXAMPLE ONLY, in your posted workbook, what range should be the print range (to ensure I understand what you're trying to do).

Dave
Rayne

ASKER
Thank you Dave :)
 I have some toggle buttons that basically control what columns I am viewing. At a specific toggle view like someStuffB, the print area will resize to columns and rows that you see. At another specific view like say somestuffA, th print area will resize to the columns that are visible and so forth. Let me know if that clarifies or needs further explanation.
Copy-of-PrintArea-UpdatedWithVie.xlsm
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
dlmille

I do.  Your code seems to want to set the print area from A1:lastCol18

Is that the print range you want?

If SomeStuffA option was selected, would you not want the range:

B18:R23 to be the print range?

Dave
Rayne

ASKER
Thanks Dave,

The top rows above the column header row [18th row] will have some info that needs to be on the printing, so that’s the reason I chose to include the top rows as well.  The ranges are here

•      someStuffA - B1:R23
•      someStuffB - B1:AA23
•      Everything - B1: AA23

So the thing is how to avoid the hidden columns in the actual printing of the worksheet under a specific view. Please let me know if you have further questions.
dlmille

Ok - I've modified to find the last row.  Now, what is selected appears to be the correct range to print, correct?

Sub setprintarea()
    Dim myrange As String
    Dim LastCol As Long
    Dim columnRow As Long
    Dim lastRow As Long
    
    'get last row on worksheet
    lastRow = Cells.Find(what:="*", LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
        
    columnRow = 18
    LastCol = Sheet1.Cells(columnRow, Columns.Count).End(xlToLeft).Column
    
     ' Sets Range from cell A1 to last column
    myrange = Cells(lastRow, columnRow).Address 'Cells(Rows.Count, columnRow).End(xlUp).Address
    ActiveSheet.PageSetup.PrintArea = "$A$1:" & myrange
    ActiveSheet.Range("$A$1:" & myrange).Select
    
End Sub

Open in new window


See attached.

Dave
Copy-of-PrintArea-UpdatedWithVie.xlsm
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rayne

ASKER
Hello Dave,

someStuffA – is printing correctly.
someStuffB – is missing columns in the print – [someStuffB] columns
Everything - should include from column B to column AA – is missing someStuffB  columns
someStuffAView.pdf
someStuffBView.pdf
EverythingView.pdf
Rayne

ASKER
By the way, I used cutePDF for this
ASKER CERTIFIED SOLUTION
dlmille

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Rayne

ASKER
Hello Dave,

No problem at all. I will reward you the points for your effort here.

 I can still see the issue: see if you can resolve it when you are free

someStuffA view printing – is printing correctly. - OK
someStuffB view printing – is missing someStuffB columns
Everything view printing- should include from column B to column AA – is missing someStuffB  columns
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Rayne

ASKER
dlmille

If columns are missing, why are they missing?  Because the print area is incorrect, or because its not unhiding what should be unhidden?

I was focused on the print area problem.  However, SomeStuffA and B columns appear to be visible and selected for the print area when I test.

What am I missing?

Dave
Rayne

ASKER
If columns are missing, why are they missing?  Because the print area is incorrect, or because it’s not un-hiding what should be unhidden?

Yes, some column sets are missing when I print the worksheet under specific views – more likely that the print area is not adjusting with the view change for some reason that I am not aware of.
I can see the columns hide/unhide when I toggle between the view.




I was focused on the print area problem.  However, SomeStuffA and B columns appear to be visible and selected for the print area when I test.

I was getting this observation

someStuffA view printing – is printing correctly. – OK (column 2-18)

someStuffB view printing – is missing someStuffB columns – its only printing column 2-12 instead of printing 2-27 –excluding columns 13-18 (not include someStuffA series)

Everything view printing- should include from column B to column AA – is missing someStuffB  columns – is only showing 2-18 columns
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rayne

ASKER
Hello Dave,

I tried to answer as clear  as possible. But let me know if you have further questions.
Rayne

ASKER
Rayne

ASKER
Dave,

Maybe to include something in the code that only sets the area under print if its visible I guess…something similar.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Rayne

ASKER
dlmille

You didn't use my latest code.  I think that's why its not working for you, lol.

Dave
PrintArea-Update-r3.xlsm
dlmille

This is not a UsedRange problem.  I am not an advocate of UsedRange because it is not reliable.  What I just posted is correct, and also my prior posting that you awarded points is also correct.

I THINK ;)

Please verify.  I was able to hit the button and see ALL the columns that should be printed, for each option button scenario.

Dave
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rayne

ASKER
Dave - yes the used range seems to work but how can I expand the print area to the upper rows above the table - that might be some work I guess..
dlmille

Have you tried what I just posted?

Dave
Rayne

ASKER
Oops, my fault Dave :)

Yup that works sweet. Perfect!! Awesome. I think I need some coffee right now, My head is jammed :)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
dlmille

No worries.  I thought I was missing something, too, then I noticed the post you posted didn't have my latest change - the one that you ended up awarding.  Perhaps you didn't get all the code?  

Cheers,

Dave
Rayne

ASKER
Hello Dave,

Perhaps, I was just playing with a bunch of file copies that have almost the same name and you know what happens in that scenario. But yes, this works perfect as expected. I was also wondering how come we both were getting different results with the same file :(
Now I know – Morning coffee is essential after 3 hours night sleep. Which I didn’t have since morning :)
Rayne

ASKER
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rayne

ASKER
Hello Dave,

I have posted another relevant question with regards to this one:
https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_27742367.html

You may look when you are free :)

Thank you