Cell Related Coding

Location: http://www.mvps.org/dmcritchie/excel/cells.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames]

Cells, last used or next free in a column

last cell in column A that belongs to the same region as cell A1.
Range("A1").End(xlDown)

last cell even with empty cells in the column you could use
Range("A65535").End(xlUp).select 'Example only
Range("A" & Rows.Count).End(xlUp).Select
Cells(Rows.Count,1).End(xlUp).Select

Range(ActiveCell, ActiveCell.End(xldown)).Select
xldown xlup xltoRight xltoLeft

The use of 65536 to identify the last row is for illustration purposes only, do not code constants into a program as the last row and hopefully the the last column can and will change between versions of Excel. Instead use Rows.Count and Columns.Count -- For examples of these in macros with toolbar icons see Toolbars page.

Last used row in column A: => 8
Worksheets("Sheet1").Range("A65536").End(xlUp).Row

Next available empty row in column A: => 9
Worksheets("Sheet1").Range("A65536").End(xlUp).Row + 1

xlDown looks for the first empty cell it finds and returns the last used row
before that first empty cell, so:

'Last used row before the first empty cell: => 3
Worksheets("Sheet1").Range("A1").End(xlDown).Row

'First empty cell in column A => 4
Worksheets("Sheet1").Range("A1").End(xlDown).Row + 1

If you want to find the last possible row all the way down column A, use
xlUp.

ActiveCell.End(xlDown)
Range("A1").End(xlDown)

As improbable as it seems you can do something similar with a Worksheet Function. The following array formula [ctrl+Shift+enter] will provide the row number for the last cell in Column A. Dave Perterson 2001-12-23 in Worksheet.Functions
=MAX(IF(ISBLANK(A1:A1000),"",ROW(A1:A1000)))

Last Column in a Row

Last cell even with empty cells in the row you could use


  Range("A256").End(xlLeft).select

  Range("A" & Columns.Count).End(xlLeft).Select

  Cells(1,Columns.Count).End(xlLeft).Select

Columns 16 through end


  Columns("P:IV").Select

  Range(Cells(1, 16), Cells(1, Columns.Count)).EntireColumn.Select

Cell Navigation

Options

tools, options, Edit, [x] move selection after entry -- Down/right/up/left

From one cell to a specific cell

Cell protection will guide you to next unprotected cell
Select a group of cells, use Enter or Tab key
Select individual cells with help of the Ctrl key, and Tab key will follow the sequence they were laid down. Example: Select A1,B2,C3,D4,E5,D6,C7,B8,A9, TabKey, then in Name box enter "MyEntry".
When you enter "MyEntry" into name box at a later time the sequence will be laid out for you.

since this page was created on June 10, 2000. Return to TOP.

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 mailto:DMcRitchie@msn.com.