Tanjima Hossain
If you are looking for ways to zoom in to fit a screen using VBA in Excel, then you will find this article helpful. By using the VBA codes of this article, you will be able to automatically fit your screen according to your preference. So, let’s get into the main article.
Download Workbook
4 Examples to Zoom to Fit Screen Using VBA in Excel
Here, we have the following dataset containing the records of marks of the students in a college for different subjects. Using this dataset, we will demonstrate how you can easily zoom your sheet to fit on your screen.
We have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.
Example-1: Using Named Range to Zoom to Fit Screen
Here, we will simply use a VBA code with a named range for the following dataset to define it and then apply the Zoom method to zoom in the defined range. Here, we can see the following sheet has a 90% zoom-in value.
- First, name the following selected range as a record or whatever you want.
- Go to the Developer tab >> Code group >> Visual Basic
After that, you will get the Visual Basic for Applications window.
- Go to the Insert tab >> Module
In this way, you will create a new module Module 1.
Step-02:
- Use the following code in your created module.
Sub fitting_scrn_1()
Range("record").Select
ActiveWindow.Zoom = True
End SubHere, we have selected the named range record and then applied the Zoom method to zoom this sheet to fit the screen.
- Press F5.
After that, you will notice that your defined range will be selected and the sheet has been zoomed in from 90% to 153%.
Read More: How to Zoom in When Zoom Slider Is Not Working in Excel
Example-2: Defining a Range of a Sheet
Here, we will define a range of the worksheet range to zoom from 90% to the corresponding percentage with which the defined range can fit on the screen.
Steps:
- Follow Step-01 of Method-1 to open a module.
- Type the following code in your created module.
Sub fitting_scrn_2()
Dim wsht As Worksheet
Set wsht = Worksheets("range")
wsht.Range("B1:F16").Select
ActiveWindow.Zoom = True
End SubHere, we have declared wsht as Worksheet and then set this variable with the sheet name range. Then we selected the range “B1:F16” and applied the Zoom method to zoom this sheet to fit into the screen.
- Press F5.
After that, you will notice that your defined range will be selected and the sheet has been zoomed in from 90% to 125%.
Read More: How to Zoom Out on Excel Online (4 Quick Ways)
Example-3: Applying Zoom Factor of One Sheet to Another to Fit in Screen Using VBA
In this method, we will show how you can use the zoom factor of one sheet to another to zoom in a similar way to the other sheet.
Here, we have a sheet zoom factor where we will apply the Zoom method to zoom in to fit the screen,
and then using the same zoom factor, we will zoom the dataset range to fit into the screen.
Steps:
- Follow Step-01 of Method-1 to open a module.
- Type the following code in your created module.
Sub fitting_scrn_3()
Dim copy_fit As Integer
Sheets("zoom factor").Select
Range("B1:G17").Select
ActiveWindow.Zoom = True
copy_fit = ActiveWindow.Zoom
Sheets("range").Select
ActiveWindow.Zoom = copy_fit
End SubCode Breakdown:
- Here, we have named the Sub Procedure as fitting_scrn_3, and then declared copy_fit as Integer
- Then, we will select the sheet zoom factor and later select the range B1:G17 of this sheet.
- After that, we applied the Zoom method to this range of this sheet.
- Later, we assigned this zoom factor used in this sheet to the variable copy_fit
- Using the variable copy_fit we have applied the same zoom factor to the sheet range.
- Press F5.
Eventually, you will notice that your defined range will be selected and the zoom factor sheet has been zoomed in from 90% to 119%.
Similarly, the range sheet has also been zoomed in from 90% to 119%.
Read More: How to Zoom in on Map Chart in Excel (5 Easy Methods)
Example-4: Zooming to Fit Screen Based on Screen Resolution Using VBA in Excel
In this section, we will zoom the following sheet based on the screen resolution using a VBA code.
Steps:
- Follow Step-01 of Method-1 to open a module.
- Type the following code in your created module.
Sub fitting_scrn_4()
Dim highest_breadth, visible_breadth As Long
Dim z_factor As Double
highest_breadth = Application.UsableWidth * 0.96
visible_breadth = ActiveWorkbook.ActiveSheet.Range("R1").Left
z_factor = highest_breadth / visible_breadth
ActiveWindow.Zoom = z_factor * 100
End SubCode Breakdown:
- We have named the Sub Procedure as fitting_scrn_4 and then declared highest_breadth, visible_breadth as Long, z_factor as Double
- Then we have set the variable highest_breadth as 96% of the usable width of the screen.
- visible_breadth will be left to Column R.
- After that, we calculated the z_factor by dividing highest_breadth by visible_breadth.
- After that, we multiplied the factor by 100 and then zoomed our screen using this value.
- Press F5.
Finally, you will be able to zoom in on the sheet from 90% to 115%.
Read More: How to Zoom Out in Excel (7 Quick Methods)
Practice Section
You can try to apply the aforementioned codes in the following Practice sheet to zoom in on this sheet to fit your screen.
Conclusion
In this article, we tried to zoom in to fit the screen using VBA in Excel. Hope you will find it useful. To explore more Excel-related articles visit our site Exceldemy. If you have any suggestions or questions, feel free to share them in the comment section.
