Print Area Reset

Rayne
Rayne used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
please note the last column is AA but it can become Z or Y depending
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
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
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
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.
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
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

Author

Commented:
By the way, I used cutePDF for this
Most Valuable Expert 2012
Top Expert 2012
Commented:
Sorry about that - one more thing I needed to fix in your code (line 14):

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, LastCol).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


Attached.

Dave
Copy-of-PrintArea-UpdatedWithVie.xlsm

Author

Commented:
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
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
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

Author

Commented:
Hello Dave,

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

Author

Commented:
Dave,

Maybe to include something in the code that only sets the area under print if its visible I guess…something similar.
Most Valuable Expert 2012
Top Expert 2012

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

Dave
PrintArea-Update-r3.xlsm
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
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..
Most Valuable Expert 2012
Top Expert 2012

Commented:
Have you tried what I just posted?

Dave

Author

Commented:
Oops, my fault Dave :)

Yup that works sweet. Perfect!! Awesome. I think I need some coffee right now, My head is jammed :)
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
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 :)

Author

Commented:

Author

Commented:
Hello Dave,

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

You may look when you are free :)

Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial