Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

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
0
-Polak
Asked:
-Polak
  • 7
  • 6
1 Solution
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
-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
 
-PolakAuthor Commented:
Hummm, okay I see what I did wrong, thank you!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now