Navigating within a Sheet and Around a Workbook

Navigating within a Sheet (#withinsheet)

Quick Navigation around the sheet

Use the name box to the left of the formula bar to get to a specific cell

Type A30000 into the namebox.

alternate: Press F5 - type A30000 in Reference - press Enter.

Use the name box to the to identify a specific range of cells

Type  A30000:B30001  into the Name Box.

To get to the last used cell

To get to the last used cell (intersection of the last used row and the last used column) -- Ctrl+End

To return to Cell A1

To get to the first cell of the worksheet use Ctrl+Home

To get to the last entry in Column A

Type A65536 into the Name Box then End then UpArrow

If there are no intervening blanks in Column A

If there are no intervening blanks in Column A you can get to the last used cell in the column using one of the following from a cell in Column A:

• Ctrl+DnArrow
• End then DnArrow
• double click on the bottom border of a cell

Some macros for quick naviation

See some macros on my Toolbars page.
generic macro  personal.xls!GotoTopOfCurrentColumn
generic macro  personal.xls!GotoBottomofCurrentColumn

Mouse Clicking for Navigation

double-clicking the border of a cell, you will see a the cursor change from a white cross to a pointer.
Double-click on a border in the direction you want to move, same as End+ArrowKey

You will find more information in Excel HELP (F1) --> index --> move --> move or copy cells

Mouse operations are poorly documented and poorly indexed in HELP, for more mouse operations see Mouse Operations on my Fill-Handle page.

Navigation to specific cells as in a Worksheet

"Down" is the normal direction that one goes after hitting Enter.  You can change this with Tools, Options, Edit, Move Cell after Enter: (Up/right/down/left).

To restrict entry to Column A or to other specific cells you can use Cell Protection. 

Restricting Navigation by using Protected Cells

Cells are protected by default, but not effective until you protect the sheet.  So you just have to unlock the cells in column A then protect the sheet.

Select Column A Format, Cells, Protection, uncheck Locked (both unchecked) Tools, Protect Sheet

Note: The TAB key will take you to A1 if you attempt to go beyond the used range.

You can unprotect any cell that you want to help with navigation.  You might want to color code cells that can or cannot be entered into.  To help you identify such cells you can use Edit, GoTo, [Special], and choose something like formulas, or blank cells, depending on which you want to color.

Navigation with the TAB key will be across the row, skipping any protected cell, and when it can't go further on a row it skips down to the next row and looks for a protected cell (repeat as necssary).

Navigation can be provided by the following or combinations of 

1. Unprotected cells among other cells with protection and locked.
2. Selection:  selections need not be contiguous or in order
     B20, C20, E20, F20, H20, N27, O27, P27, m34, B21
   Problem is if you break the selection by not tabbing to the next cell in selection.
3. Event macro to guide you after change Event or after a selection Change event.

Restricting Navigation to Columns A, B, C using an Event Macro

Another way to restrict Navigation is with an Event Macro.

  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     If Target.Column > 3 Then
        ActiveCell.Offset(1, 1 - Target.Column).Select
     End If
  End Sub

You could set a cell to be checked within the macro to ignore the jump to next row so you could do some maintenance.

You would use the RtArrow key though you could change the direction of the Enter key from Down to Right.  Use of the TAB key as mentioned before would take you to A1 if you attempt to go outside the used range.

Navigation to Another Sheet (#anothersheet)

 Private Sub Worksheet_BeforeDoubleClick(ByVal _
      Target As Range, Cancel As Boolean)
   Cancel = True   'Get out of edit mode 
   Worksheets(Trim(Target.Value)).Activate
 End Sub

Navigating to Code in the Visual Basic Editor (#VBE)

  personal.xls!MacroDialogBox (on Toolbars Page)
 GoToSub (on BuildTOC page)

• Build Table of Contents has quite a bit of navigaton via hyperlinks.
• Event macros can be used to restrict Navigation.
• Fill-Handles and Replication, also see topic: Use of Mouse and keys to move, and insert rows or columns
• Last Cell considerations for eliminating rows and columns that are actually empty but are in the used range.  Making a specific cell become the Last Used Cell.
• Navigating to another sheet or to cells with macros.  Also see Worksheet VBA coding and Cell Related Coding.
• Protection of worksheet cells, worksheet
• Shortcut keys.
• Toolbars has some macros for navigation around the sheet
• Validation Checking, Excel provides a means of checking manually entered data and warning or blocking incorrect usage.

Visit [my Excel home page]   [Index page]   [Excel Onsite Search]   [top of this page]

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2006,  F. David McRitchie,  All Rights Reserved