Print Area Reset

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

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

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

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

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.

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

Select allOpen in new window

See attached.

Dave
Copy-of-PrintArea-UpdatedWithVie.xlsm

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

By the way, I used cutePDF for this

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

Copy-of-Copy-of-PrintArea-Update.xlsm

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

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

Hello Dave,

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

Copy-of-Copy-of-PrintArea-Update.xlsm

Dave,

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

something like this maybe:
https://www.experts-exchange.com/questions/26422808/VBA-Macro-Set-Relative-Print-Area.html

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

Dave
PrintArea-Update-r3.xlsm

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

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..

Have you tried what I just posted?

Dave

Oops, my fault Dave :)

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

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

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

Dave,

Here is a currentRegion question - when you are free
https://www.experts-exchange.com/questions/27727974/Current-Region-Usage.html

Hello Dave,

I have posted another relevant question with regards to this one:
https://www.experts-exchange.com/questions/27742367/Print-All-Views.html

You may look when you are free :)

Thank you