Fit Worksheet to Window in Excel

Have you ever felt Excel needed a Fit-Document-to-Window-Width feature like the millions of PDF Readers out there? Wouldn’t it be great if you could automatically fit your document to your current window size? The irony is, Excel already has all it needs to deliver that feature to you. Since spreadsheets come in various shapes and sizes, you might want to fit the width, or height, or the entire sheet to your screen. In this post, we will take a look at a routine that I wrote that you can use to fit any sheet to your window. And for you lazy bunch, I have an add-in that will let you call these macros from the Excel Ribbon.

Did you know that Excel has a Zoom to Selection feature?

Excel’s Zoom to Selection feature will set the Zoom level of the worksheet such that the selected range of cells fills the entire window. This feature can be accessed through the View tab.

In VBA, you can access this feature by simply setting the Zoom Property of the ActiveWindow object to TRUE, after making the selection of course.

ActiveWindow.Zoom = True

Excel already does the heavy lifting for us, all we have to do now is make a few tactful selections, and call this feature. This is where the UsedRange property of the Worksheet object comes to the rescue.

Excel ever so vigilantly tracks the changes you make in a sheet and remembers the closest and farthest, row and column you have tampered with. That is how it determines the Used Range.

If we make a selection of the used range, and call the zoom to selection feature, we will fit the entire sheet into the window. Similarly, if we select the first row of the used range, we will fit the width of the sheet to the window. Do I even need to tell you how to fit the height of the sheet to the window?

Sometimes Excel, in all its zeal, thinks you have used more cells in a sheet than you actually did. I recommend you read a post on Getting the Actual Used Range that I wrote, and maybe incorporate it into the Routine below.

I added some extra lines here and there to make my sub friendly to power users who want to use my sub in their projects, and to the casual user calling it from the Ribbon. This will be handy for people who are designing dashboards, and to people who want to quickly size up the sheets they are working with.

'#####################################################################
'Declare an Enum to let the user easily select what ZoomType they want
Public Enum s2xlFitToSheetType
    s2xlFitToSheetWidth
    s2xlFitToSheetHeight
    s2xlFitToSheetFull
    s2xlFitToSheetReset
End Enum

'#####################################################################
Sub AutoZoomWorkSheet( _
    ByRef WhichSheet As Worksheet, _
    ByVal ZoomType As s2xlFitToSheetType _
    )

'Declare all the Objects
Dim TheActiveSheet As Object, _
    TheSelectedCells As Range, _
    TheUsedRange As Range

'Remember what the activesheet was
'When using this sub in your projects this might come in handy
Set TheActiveSheet = Application.ActiveSheet

'Activate the sheet you want to work with
WhichSheet.Activate

'Remember what range the user had selected before making a
'new selection for the the zoom to selection.
Set TheSelectedCells = Application.Selection
'The user's selection is sacred (for the add-in)

'Get the usedrange of the sheet you want to work with
Set TheUsedRange = WhichSheet.UsedRange

If ZoomType = s2xlFitToSheetReset Then
    ActiveWindow.Zoom = False
Else

    Select Case ZoomType
        Case s2xlFitToSheetWidth
            'Select the First Row of the Used Range to
            'Fit-to-Width
            TheUsedRange.Rows(1).Cells.Select

        Case s2xlFitToSheetHeight
            'Select the First Column of the Used Range
            'to Fit-to-Height
            TheUsedRange.Columns(1).Cells.Select
            ActiveWindow.Zoom = True
        Case s2xlFitToSheetFull
            'Select the entire Used Range to Fit the
            'Whole Sheet
            TheUsedRange.Select
            ActiveWindow.Zoom = True
    End Select

    'Call the Zoom to Range Feature
    ActiveWindow.Zoom = True

    'Set the selection back to what it was
    TheSelectedCells.Select

    'Although the Zoom Level is correctly set by the
    'Activewindow.zoom property, the content might
    'be out of view depending on the users cell
    'selection before calling this command
    'so we try to scroll back to the first visible cell
    With TheUsedRange.SpecialCells(xlCellTypeVisible).Cells(1, 1)
        ActiveWindow.ScrollColumn = .Column
        ActiveWindow.ScrollRow = .Row
    End With

End If

'Set the active sheet back to what it was
TheActiveSheet.Activate

End Sub
'#####################################################################

Download the add-in below. The ‘modZoom’ module has the main routine; ‘modCover’ has a few examples on how you can call the sub in your projects. There are additional modules that make the Ribbon work, which you can ignore.

You will find that the Add-in adds a group of four buttons to the ‘View’ tab of the Excel Ribbon. Have fun with it!

I have added a lot of comments in the code letting you know about the purpose of most of the lines, as always. Feel free to reach out to me if you have any feedback or questions.

Downloads

Note that if any errors occur in any of your code, it can throw the ribbon buttons out-of-order. You’d have to reinstall the add-in to get it back up. A lot of code is required to make an add-in survive this defect, and is beyond the scope of this post.