[Excel VBA] How to Zoom to Fit a Specific Cell Range

Introduction

Sometimes you want users to focus on a specific table or chart area on a large worksheet. Adjusting the zoom slider manually is tedious, but with VBA, you can instantly automatically adjust the zoom level so that a specified cell range fits perfectly in the window.

In this article, I will explain this convenient technique using the special code ActiveWindow.Zoom = True.

Completed VBA Code

Below is the VBA code that zooms in on the range B2:L25 of “Sheet1” to fit the window size.

Sub ZoomToFitRange()

    ' Declare variables
    Dim targetRange As Range
    
    ' --- 1. Set the cell range you want to display ---
    Set targetRange = ThisWorkbook.Worksheets("Sheet1").Range("B2:L25")
    
    ' --- 2. Select the target range ---
    ' This selection becomes the basis for Zoom = True
    targetRange.Select
    
    ' --- 3. Automatically adjust zoom to fit the selection ---
    ' This single line changes the zoom rate so the selection fits in the window
    ActiveWindow.Zoom = True
    
    ' --- 4. Deselect the range (Optional but recommended) ---
    ' Select the top-left cell to make it look cleaner
    targetRange.Cells(1, 1).Select
    
    MsgBox "Displayed the specified range to fit the screen."

End Sub

Explanation of Key Points

1. Selecting the Target Range: .Select

targetRange.Select

The first step of this technique is to use the .Select method to highlight the cell range you want to fill the screen. This “selected state” serves as the reference for the next step.

2. Executing Zoom: ActiveWindow.Zoom = True

ActiveWindow.Zoom = True

This is the core of this process. The ActiveWindow.Zoom property is usually used to set a numeric zoom percentage (like 100 or 75).

However, if you set this to the special value True, Excel interprets it as a command to “automatically adjust the zoom level so the currently selected range fits exactly within the window.” This function is equivalent to the “Zoom to Selection” command found in the Excel View tab.

3. Deselecting

targetRange.Cells(1, 1).Select

If the range remains selected after zooming, it might look messy. Although optional, selecting the top-left cell (.Cells(1, 1)) at the end clears the highlight and finishes the process with a clean look for the user.

Summary

The steps to display a specified range to fill the screen are as follows:

1. Select the cell range you want to show using .Select.
2. Execute ActiveWindow.Zoom = True to automatically adjust the zoom to the selection.
3. (Recommended) Select a single cell at the end to tidy up the appearance.

This simple macro is very useful for dashboard features that present specific tables or charts to users, or when you want to create a “presentation mode” within Excel.