This page describes two VBA procedures that you can use to zoom in on a specific range of a worksheet, or to center the screen on a specific cell.  These procedures are intended to be called by other VBA procedures -- they don't provide much functionality as stand-alone procedures. 

Zooming On A Range 

This procedure will zoom the screen so that a specified range will fill the entire screen.  Before using this procedure, there are two considerations that you need to remember: 

The maximum zoom level in Excel if 400%.  Therefore, if the specified range is too small, the worksheet is zoomed to the maximum level. 

You cannot zoom in on an arbitrary range.  The Zoom area must fill the entire screen.  Therefore, either the number of rows or the number of columns must be adjusted so that the zoomed area will fill the entire screen.  The PreserveRows parameter to the ZoomToRange procedure controls whether the number of rows if fixed (and the number of columns is adjusted) or the number of columns is fixed (and the number of rows is adjusted). 

Sub ZoomToRange(ByVal ZoomThisRange As Range, _
    ByVal PreserveRows As Boolean)

Dim Wind As Window

Set Wind = ActiveWindow
Application.ScreenUpdating = False
'
' Put the upper left cell of the range in the top-left of the screen.
'
Application.Goto ZoomThisRange(1, 1), True

With ZoomThisRange
    If PreserveRows = True Then
        .Resize(.Rows.Count, 1).Select
    Else
        .Resize(1, .Columns.Count).Select
    End If
End With

With Wind
    .Zoom = True
    .VisibleRange(1, 1).Select
End With

End Sub

You can call this procedure from another procedure to zoom in on a specific range.  For example, to zoom in on cells F20:K40, and allow Excel to adjust the number of columns as required, use 

    ZoomToRange ZoomThisRange:=Range("F20:K40"), PreserveRows:=True

Centering The Screen On A Cell

This procedure will allow you to center the screen on a specific cell.  For example, you can call the procedure to put cell S50 at the center of the screen.  Unlike the Application.Goto method, which puts the specified cell at the upper left corner of the screen, this procedure will put the specified cell in the center.  If the screen cannot be scrolled to center on a cell (e.g., you can't center on C5 because there are not enough rows above row 5 or columns to the left of column C to fill out the screen), it comes as close as possible. 

Sub CenterOnCell(OnCell As Range)

Dim VisRows As Integer
Dim VisCols As Integer

Application.ScreenUpdating = False
'
' Switch over to the OnCell's workbook and worksheet.
'
OnCell.Parent.Parent.Activate
OnCell.Parent.Activate
'
' Get the number of visible rows and columns for the active window.
'
With ActiveWindow.VisibleRange
    VisRows = .Rows.Count
    VisCols = .Columns.Count
End With
'
' Now, determine what cell we need to GOTO. The GOTO method will
' place that cell reference in the upper left corner of the screen,
' so that reference needs to be VisRows/2 above and VisCols/2 columns
' to the left of the cell we want to center on. Use the MAX function
' to ensure we're not trying to GOTO a cell in row <=0 or column <=0.
'
With Application
    .Goto reference:=OnCell.Parent.Cells( _
        .WorksheetFunction.Max(1, OnCell.Row + _
        (OnCell.Rows.Count / 2) - (VisRows / 2)), _
        .WorksheetFunction.Max(1, OnCell.Column + _
        (OnCell.Columns.Count / 2) - _
        .WorksheetFunction.RoundDown((VisCols / 2), 0))), _
     scroll:=True
End With

OnCell.Select
Application.ScreenUpdating = True

End Sub

You can then call this procedure to center the screen on a cell.  For example to center the screen on S50, use 

CenterOnCell Range("S50")

If you pass in a range containing more than one cell, the entire range will be centered on the screen.