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.

Professor JMicrosoft Excel ExpertCommented:
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

-PolakAuthor Commented:
JimJam your code does the same thing 86% zoom on sheet1 76% zoom on sheet2.....
Professor JMicrosoft Excel ExpertCommented:
does both of the sheets has similar amount data in their cells?
Determine the Perfect Price for Your IT Services

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

-PolakAuthor Commented:
I attached the example. Yes sheet1 and sheet2 are identical, except in sheet2 the row heights have been modified.
Professor JMicrosoft Excel ExpertCommented:
check the file that i hereby attached.  let me know how the zoom works.
sssss.xlsm
-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
Professor JMicrosoft Excel ExpertCommented:
OK,  please see attached file. it is 100% working. I checked it myself and it works.
F--Auto-Zoom-VBA.xlsm
-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.....
Professor JMicrosoft Excel ExpertCommented:
it depends on the monitor of your screen.  min eis 90 and next one 103
-PolakAuthor Commented:
Given that the "Used Range" is the same between both sheets why the difference just because sheet2 has some modified row heights?
Professor JMicrosoft Excel ExpertCommented:
yes exaclty
Row Heights affects zoom range and that is why, there is difference.
-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....
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

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