Odd Behavior with Used Range Zoom Macro

Experts,

Please see the attached file and the below code:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    ActiveSheet.UsedRange.Resize(ActiveSheet.UsedRange.Columns.Count).Select
    ActiveWindow.Zoom = True: ActiveWindow.VisibleRange(1, 1).Select
End Sub

Open in new window

Why does the zoom range set differently between sheet1 and sheet2 when some of the rows are different heights but it is the same number of used columns?

Is there a better way to write this? I would like for it to only set the active zoom based columns with used cells.....
EE-UsedZoomRange-Test.xlsm
LVL 1
-PolakAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamCommented:
use this instead of that

Sub UsedRangeZoom()
    'set zoom for active sheet to columns with data
Application.EnableEvents = True
    Application.WindowState = xlMaximized
    ActiveSheet.UsedRange.Select
    ActiveWindow.Zoom = True
End Sub

Open in new window

0
-PolakAuthor Commented:
JimJam your code does the same thing 86% zoom on sheet1 76% zoom on sheet2.....
0
ProfessorJimJamCommented:
does both of the sheets has similar amount data in their cells?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

-PolakAuthor Commented:
I attached the example. Yes sheet1 and sheet2 are identical, except in sheet2 the row heights have been modified.
0
ProfessorJimJamCommented:
check the file that i hereby attached.  let me know how the zoom works.
sssss.xlsm
0
-PolakAuthor Commented:
I got a runtime error 91 as soon as tired to open that file.
I've reattached your workbook with the bits of code not pertinent to this question disabled. Lets just focus on the Zoom issue not any of the environment things you are helping me on in another question....

For me your code doesn't set the zoom at all for used cells in the attached. Moreover your code doesn't even allow me to switch between sheet1 and sheet2.
Copy-of-sssss.xlsm
0
ProfessorJimJamCommented:
OK,  please see attached file. it is 100% working. I checked it myself and it works.
F--Auto-Zoom-VBA.xlsm
0
-PolakAuthor Commented:
Question, what are you zoom %'s on sheet1 vs. sheet2? (mine are 86% vs 72%) pretty much the same result as my original code.....
0
ProfessorJimJamCommented:
it depends on the monitor of your screen.  min eis 90 and next one 103
0
-PolakAuthor Commented:
Given that the "Used Range" is the same between both sheets why the difference just because sheet2 has some modified row heights?
0
ProfessorJimJamCommented:
yes exaclty
Row Heights affects zoom range and that is why, there is difference.
0
-PolakAuthor Commented:
Going back to my original need, I only want the zoom range to set based upon used COLUMNS, I do not want row heights to effect anything; my original code shouldn't behave that way yet it does....
0
Ejgil HedegaardCommented:
You select the entire used range, and make the window zoom on that selection.
Then the rows also need to fit the window,
To only zoom on the columns, select the first row only.

So the macro will be

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    ActiveSheet.UsedRange.Rows(1).Select
    ActiveWindow.Zoom = True
    ActiveWindow.VisibleRange(1, 1).Select
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
-PolakAuthor Commented:
Hummm, okay I see what I did wrong, thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.