Excel VBA: Zoom to Fit Screen (4 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.


Zoom to Fit Screen Using VBA in Excel: 4 Suitable Examples

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.

excel vba zoom to fit 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.

excel vba zoom to fit screen

Step-01:

  • First, name the following selected range as a record or whatever you want.

named range

  • Go to the Developer tab >> Code group >> Visual Basic.

named range

After that, you will get the Visual Basic for Applications window.

  • Go to the Insert tab >> Module.

named range

In this way, you will create a new module Module 1.

excel vba zoom to fit screen

Step-02:

  • Use the following code in your created module.
Sub fitting_scrn_1()

Range("record").Select
ActiveWindow.Zoom = True

End Sub

Here, we have selected the named range record and then applied the Zoom method to zoom this sheet to fit the screen.

named range

  • 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%.

excel vba zoom to fit screen


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.

excel vba zoom to fit 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 Sub

Here, 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.

defining range

  • 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%.

defining range


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,

excel vba zoom to fit screen

and then using the same zoom factor, we will zoom the dataset range to fit into the screen.

zoom factor

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 Sub

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

zoom factor

  • 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%.

zoom factor

Similarly, the range sheet has also been zoomed in from 90% to 119%.

excel vba zoom to fit screen


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.

excel vba zoom to fit screen

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 Sub

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

screen resolution

  • Press F5.

Finally, you will be able to zoom in on the sheet from 90% to 115%.

screen resolution


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.

practice


Download Practice Workbook


Conclusion

In this article, we tried to zoom in to fit the screen using VBA in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles


<<Go Back to How to Zoom in Excel | Excel Worksheets | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo