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.

REFERENCES

"Function Reference," version 4.0 pages 172-173, 207-209, 221, 394, 450 "Function Reference," version 3.0, pages 93, 115-116, 122, 222, 246 "Functions and Macros," version 2.2 for the Macintosh , pages 227, 239-240, 243, 289 "Functions and Macros," version 2.1, pages 300-301, 305, 357, 367

KBCategory: kbusage KBSubcategory: Additional reference words: 2.00 2.01 2.10 2.20 3.00 4.00 4.00a 5.00 5.00c 5.00a 7.00 Keywords : kbusage Version : 2.X 3.x 4.x 5.x 7.0 | 2.X 3.00 | Platform : MACINTOSH OS/2 WINDOWS THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Last reviewed: March 27, 1997 ©1997 Microsoft Corporation. All rights reserved. Legal Notices.