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

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.

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

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.

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

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,

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

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.

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

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.

practice


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.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo