You can use the following methods to specify the zoom in Excel by using VBA:
Method 1: Set Zoom to Specific Amount
Sub ZoomToAmount()
ActiveWindow.Zoom = 150
End Sub
This particular macro will set the zoom to be 150% for the currently active sheet.
Method 2: Set Zoom to Currently Selected Range
Sub ZoomToSelection()
Selection.Select
ActiveWindow.Zoom = True
End Sub
This particular macro will zoom into the currently selected range.
Method 3: Set Zoom to Specific Range
Sub ZoomToRange()
Range("C1:E5").Select
ActiveWindow.Zoom = True
End Sub
This particular macro will zoom into the range C1:E5.
The following examples show how to use each method in practice with the following sheet in Excel that contains a dataset about various basketball players:
Example 1: Set Zoom to Specific Amount
We can use the following macro to set the zoom of the currently active sheet to be 150%:
Sub ZoomToAmount()
ActiveWindow.Zoom = 150
End Sub
When we run this macro, the sheet will automatically have a zoom of 150%:
Note that the default zoom is 100.
By setting the zoom to a value greater than 100, we zoom in.
To zoom out, set the zoom to a value less than 100.
Example 2: Set Zoom to Currently Selected Range
We can use the following macro to set the zoom of the currently active sheet to be the currently selected range:
Sub ZoomToSelection()
Selection.Select
ActiveWindow.Zoom = True
End Sub
For example, suppose we select the range A7:C11.
When we run this macro, the sheet will automatically zoom into this range:
Example 3: Set Zoom to Specific Range
We can use the following macro to set the zoom of the currently active sheet to the range C1:E5:
Sub ZoomToRange()
Range("C1:E5").Select
ActiveWindow.Zoom = True
End Sub
When we run this macro, the sheet will automatically zoom into this range:
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
How to Count Number of Rows in Range Using VBA
How to Loop Through Worksheets Using VBA
How to Unhide All Rows Using VBA
