Microsoft KB Archive/107386

= Microsoft Knowledge Base =

Moving Active Cell to Upper-Left Corner of Excel Window
Last reviewed: March 27, 1997

Article ID: Q107386

2.X 3.x 4.x 5.x 7.0 | 2.X 3.00 | 2.X 3.x 4.x 5.00 5.00a

WINDOWS            | OS/2     | MACINTOSH kbusage

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.x
 * Microsoft Excel for Windows 95, version 7.0
 * Microsoft Excel for OS/2, versions 2.x, 3.x
 * Microsoft Excel for the Macintosh, versions 2.x, 3.x, 4.x, 5.0, 5.0a

SUMMARY
The following macros find and display the active cell on a Microsoft Excel worksheet; the macro then positions the active window such that the active cell is in the upper-left corner of the pane.

MORE INFORMATION
If the active document has not been split into panes, the active cell will be moved to the upper-left corner of the document window or pane.

Microsoft Excel versions 5.x and 7.0
Sub GotoUpperCorner1 ' This will move the active cell to the top left cell. Application.Goto ActiveCell, True End Sub

Sub GotoUpperCorner2 ' This statement moves cell B100 to the top left cell. Application.Goto Range("B100"), True End Sub

Microsoft Excel versions 3.0, 4.0, and 5.0
Before you run the following macro, do either of the following:

 Select the cell that you want to appear in the upper-left corner -or-  Replace the ACTIVE.CELL function in the following example with a reference to the cell that you want to appear in the upper-left corner: A1: Upper_Corner A2: =FORMULA.GOTO(ACTIVE.CELL,TRUE) A3: =RETURN 

Explanation of Macro Code
A1: Macro Name (Select this cell, choose Define Name from the Formula Menu, and define this name as a command macro).

A2: Go to the active cell. If the second argument is TRUE the macro places the cell in the upper-left corner of the window.

A3: End the macro.

Microsoft Excel versions 1.x, 2.x
If you are using a version of Microsoft Excel earlier than 3.0, use the following. This macro will move the currently selected cell to the upper- left corner of the window.

A1: Upper_Corner A2: =SHOW.ACTIVE.CELL A3: =GET.WINDOW(17) A4: =COLUMN(ACTIVE.CELL)-INDEX(GET.WINDOW(13),1,A3) A5: =ROW(ACTIVE.CELL)-INDEX(GET.WINDOW(14),1,A3) A6: =HLINE(A4) A7: =VLINE(A5) A8: =RETURN

Explanation of Macro Code
A1: Macro Name (Select this cell, choose Define Name from the Formula Menu, and define this name as a command macro).

A2: Scrolls the active window so the active cell becomes visible. If an object is selected, the #VALUE! error value will be returned.

A3: Returns the number of the active pane.

A4: Returns the number of columns between the active cell and the leftmost column in the display.

A5: Returns the number of rows between the active cell and the top column in the display.

A6: Scrolls right based on the number returned in cell A4.

A7: Scrolls up based on the number returned in cell A5.

A8: Ends the macro.